When a comparison is performed between a value of CHAR type (CHAR, VARCHAR, LONG VARCHAR) and a value of NCHAR type (NCHAR, NVARCHAR, LONG NVARCHAR), SQL Anywhere uses inference rules to determine the type in which the comparison should be performed. Generally, if one value is based on a column reference and the other is not, the comparison is performed in the type of the value containing the column reference.
The inference rules revolve around whether a value is based on a column reference. In the case where one value is a variable, a host variable, a literal constant, or a complex expression not based on a column reference and the other value is based on a column reference, then the constant-based value is implicitly cast to the type of the column-based value.
Following are the inference rules, in the order in which they are applied:
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.
Employees.GivenName = N'Susan' compares a CHAR column (Employees.GivenName) to the literal N'Susan'. The value N'Susan' is cast 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 cast 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;
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|