How do you set collation for an entire query in SQL Server in order to do a case insensitive search?

Question

I'm using ColdFusion 9 to access a SQL Server 2008 database. The database is set to use case sensitive collation. I would like to do a case insensitive search against it. Something like:

 select field
 from table
 where field2 = 'test'  COLLATE SQL_Latin1_General_CP1_CI_AS

The complication is that my where clause uses an IN operator to search against a comma delimited list of string values. This does not seem to work unless I set the collation after each value:

 select field
 from table
 where field2 IN ('test' COLLATE SQL_Latin1_General_CP1_CI_AS, 'test2' COLLATE SQL_Latin1_General_CP1_CI_AS, ...)

I could do this with a search and replace, but I would also like to include this list in a cfQueryParam, which handles delimiters itself. I'm not sure if that's possible, and if it is then it is getting very messy. So I'd like to instead set the collation for the entire query. In Oracle this can be done before the query by altering the session settings. Is something like this possible in SQL Server, such as:

 SET COLLATE SQL_Latin1_General_CP1_CI_AS
 select field
 from table
 where field2 IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="test1,test2,...">)

Solution

It works if you put the collate statement before the IN clause:

WHERE Column COLLATE SQL_Latin1_General_CP1_CI_AS IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

However, I think the bind variables may cause it to have an unintended side effect. When I re-ran the exact same query directly afterward - just without the COLLATE - it still performed a case insensitive search.

WHERE Column IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

But change the query in some way, so the execution plan is not cached (like add AND 1 = 1 to the where clause) and the results are case sensitive again. Just something to keep in mind.

Update:

To avoid the caching issue, one possibility is to use a derived table. Then apply the collate statement to the SELECT list instead of the IN clause:

SELECT t.Column
FROM  (
        SELECT Column COLLATE SQL_Latin1_General_CP1_CI_AS AS Column
        FROM YourTable
      ) t 
WHERE t.Column IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

That said, if you always want to perform a case insensitive search on this column, it would be better to alter the column's collation as cfqueryparam suggested.

Comments

[Nicholas]

Thank you, but the database must remain case sensitive. I need to setting to affect just this query, and preferably not any other queries within the same session.

[Nicholas]

We had originally considered something similar, but we really wanted to avoid the performance hit of those functions in the where clause.

[Nicholas]

We're seeing similar caching behavior, but as you said it only appears to affect that exact same query, so since we don't use it anywhere else we're moving ahead with this solution. Thanks!