title: How do you set collation for an entire query in SQL Server in order to do a case insensitive search?
body: <p>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:</p>
  
  <pre><code> select field
   from table
   where field2 = 'test'  COLLATE SQL_Latin1_General_CP1_CI_AS
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> select field
   from table
   where field2 IN ('test' COLLATE SQL_Latin1_General_CP1_CI_AS, 'test2' COLLATE SQL_Latin1_General_CP1_CI_AS, ...)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> SET COLLATE SQL_Latin1_General_CP1_CI_AS
   select field
   from table
   where field2 IN (&lt;cfqueryparam cfsqltype="cf_sql_varchar" value="test1,test2,..."&gt;)
  </code></pre>