I just tried a simple exclusion query of the form
where C1 is a varchar column.
That works very fast if C1 and C2 are both varchar types.
It runs horrible slow when C1 is a nvarchar type and C2 is not.
It runs fast again when C1 is explicetely casted as in
Is this kind of implicit string conversion between varchar and nvarchar an exclusion for a "sargable" predicate?
(I'm not dealing with this particular case, just asking if this is a general rule of thumb: To cast any search predicate when using IN expressions and the like?)
I'm running SA 18.104.22.1681.
The short answer is yes.
To ensure safety in doing comparison operations, SQL Anywhere must in many cases avoid using an index for value lookups if the domain of the lookup value is not identical to the domain of the indexed column.
There is a whitepaper that explains this in detail for all other data types (since the paper was written for Vrsion 9, it contains nothing about CHAR - NCHAR comparisons.
answered 28 Nov '09, 03:08