UDF 中的 COLLATE 未按预期工作

我有一个带有文本字段的表格。我想选择文本全部大写的行。该代码按其应有的方式工作，并返回ABC:

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
txt COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(txt)

然后我创建 UDF（按照建议here):

CREATE FUNCTION [dbo].[fnsConvert]
(
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
)
RETURNS NVARCHAR(2000)
AS
    BEGIN
        IF ( @c = 'SQL_Latin1_General_CP1_CS_AS' )
            SET @p = @p COLLATE SQL_Latin1_General_CP1_CS_AS
        RETURN @p    
    END

并按如下方式运行它（对我来说这看起来是等效的代码）：

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
dbo.fnsConvert(txt, 'SQL_Latin1_General_CP1_CS_AS') = UPPER(txt)

但是，这会返回ABC也cdf.

为什么会这样，我该如何让它发挥作用？

PS 我在这里需要 UDF 才能从 .Net LINQ2SQL 提供程序调用区分大小写的比较。

变量不能有自己的排序规则。它将始终使用服务器的默认值。检查一下：

--我声明了三个变量，每个变量都有自己的排序规则 - 至少有人可能这么认为：

DECLARE @deflt VARCHAR(100) = 'aBc'; --Latin1_General_CI_AS in my system
DECLARE @Arab VARCHAR(100) = 'aBc' COLLATE Arabic_100_CS_AS_WS_SC;
DECLARE @Rom VARCHAR(100) = 'aBc' COLLATE Romanian_CI_AI

——现在检查一下。所有三个变量都被视为系统的默认排序规则：

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT @deflt AS Deflt, @Arab AS Arab, @Rom AS Rom'
                                          ,N'@deflt varchar(100), @Arab varchar(100),@Rom varchar(100)'
                                          ,0);

/*
name    system_type_name    collation_name
Deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Latin1_General_CI_AS
Rom     varchar(100)        Latin1_General_CI_AS
*/

--现在我们检查“aBc”与“ABC”的简单比较

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CI  CI*/

--但是我们可以为一个给定的操作指定排序规则！

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' COLLATE Arabic_100_CS_AS_WS_SC THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' COLLATE Romanian_CI_AI THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CS  CI*/

--但是表的列会有不同的行为：

CREATE TABLE #tempTable(deflt VARCHAR(100)
                       ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                       ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO #tempTable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM #tempTable',NULL,0);
DROP TABLE #tempTable;

/*
name    system_type_name    collation_name
deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Arabic_100_CS_AS_WS_SC
Rom     varchar(100)        Romanian_CI_AI
*/

--这也适用于声明表变量。比较“知道”指定的排序规则：

DECLARE @TableVariable TABLE(deflt VARCHAR(100)
                            ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                            ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO @TableVariable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT CASE WHEN tv.deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN tv.Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN tv.Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom
FROM @TableVariable AS tv

/*CI    CS  CI*/

更新一些文档

在这个链接您可以阅读有关详细信息。整理不改变值. It 应用规则（相关NOT NULL它不会更改值，而只是添加规则是否NULL可以设置也可以不设置）。

文档说得很清楚

是一个子句，可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则转换。

稍后你会发现

1. 创建或更改数据库
2. 创建或更改表列
3. 转换表达式的排序规则

更新2：解决方案的建议

如果您想控制比较是进行 CS 还是 CI，您可以尝试以下操作：

DECLARE @tbl TABLE(SomeValueInDefaultCollation VARCHAR(100));
INSERT INTO  @tbl VALUES ('ABC'),('aBc');

DECLARE @CompareCaseSensitive BIT = 0;
DECLARE @SearchFor VARCHAR(100) = 'aBc';

SELECT *
FROM @tbl 
WHERE (@CompareCaseSensitive=1 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CS_AS)
   OR (ISNULL(@CompareCaseSensitive,0)=0 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CI_AS);

With @CompareCaseSensitive set to 1它只会返回aBc, with NULL or 0它将返回两行。

这是——肯定的！ - 性能比 UDF 好得多。