Should I cast expressions for IN search conditions to the matching type?

[VolkerBarth]

I just tried a simple exclusion query of the form

select * from T1 where C1 not in (select C2 from T2)

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

select * from T1 where cast(C1 as varchar) not in (select C2 from T2)

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 11.0.1.2331.

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

Glenn, thanks for the pointer. The whitepaper makes it all clear, and it seems understandable that CHAR vs. NCHAR falls in the category of "not the same type", too. The learning curve goes on ever and ever:)