sql-server - 如何在 SQL Server 中为整个查询设置排序规则以进行不区分大小写的搜索？

Question

我正在使用 ColdFusion 9 访问 SQL Server 2008 数据库。数据库设置为使用区分大小写的排序规则。我想对其进行不区分大小写的搜索。像这样的东西:

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

复杂的是，我的 where 子句使用 IN 运算符来搜索以逗号分隔的字符串值列表。这似乎不起作用，除非我在每个值之后设置排序规则:

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

我可以通过搜索和替换来完成此操作，但我还想将此列表包含在 cfQueryParam 中，它自己处理定界符。我不确定这是否可能，如果是的话，它会变得非常困惑。所以我想为整个查询设置排序规则。在 Oracle 中，这可以在查询之前通过更改 session 设置来完成。在 SQL Server 中是否可能出现这样的情况，例如:

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

Answer 1

如果将 collat​​e 语句放在 IN 子句之前，它会起作用:

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

但是，我认为绑定(bind)变量可能会导致它产生意想不到的副作用。当我之后直接重新运行完全相同的查询时 - 只是没有 COLLATE - 它仍然执行不区分大小写的搜索。

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

但以某种方式更改查询，因此不缓存执行计划(如将 AND 1 = 1 添加到 where 子句)并且结果再次区分大小写。只是要记住一些事情。

更新:

为避免缓存问题，一种可能是使用派生表。然后将整理语句应用于 SELECT 列表而不是 IN 子句:

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">
      ) 

就是说，如果您总是想要对此列执行不区分大小写的搜索，最好按照 cfqueryparam 的建议更改列的排序规则。