|
When a comparison is performed between a CHAR type (CHAR, VARCHAR, LONG VARCHAR) and an NCHAR type (NCHAR, NVARCHAR, LONG NVARCHAR), SQL Anywhere uses inference rules to determine whether the NCHAR value can, and should, be coerced to the CHAR type. A value can be coerced if it is a literal constant, a variable, a host variable, or a complex expression not based on a column reference. Generally, when an NCHAR value is compared to a CHAR column, the comparison is performed as CHAR if the NCHAR value can be coerced to CHAR; otherwise, it is performed as NCHAR.
Following are the inference rules, in the order in which they are applied:
If there is any non-coercible NCHAR value, then all CHAR values are converted to NCHAR, and the comparison is done as NCHAR.
Else, if there is any non-coercible CHAR value, then all NCHAR values are converted to CHAR, and the comparison is done as CHAR.
It is important to consider the setting for the on_charset_conversion_failure option if you anticipate NCHAR to CHAR conversions since this option controls behavior if an NCHAR character cannot be represented in the CHAR character set. For further explanation, see Converting NCHAR to CHAR.
Else, if there is a mix of coercible CHAR and NCHAR values (that is, all values are coercible), then all CHAR values are converted to NCHAR, and the comparison is done as NCHAR.
The condition Employees.GivenName = N'Susan' compares a CHAR column (Employees.GivenName) to the literal N'Susan'. The value N'Susan' is coerced to CHAR, and the comparison is performed as if it had been written as:
Employees.GivenName = CAST( N'Susan' AS CHAR )
Alternatively, the condition Employees.GivenName = T.nchar_column would find that the value T.nchar_column can not be coerced to CHAR. The comparison would be performed as if it were written as follows, and an index on Employees.GivenName can not be used:
CAST( Employees.GivenName AS NCHAR ) = T.nchar_column