sql - 如何在SQL Server中获得排序规则的区分大小写的版本？

Question

有没有办法获取在查询中使用的区分大小写的排序规则？

假设该查询可用于具有不同归类的数据库，其中一些不区分大小写，并且具有不同的区域性。 （例如多个客户）

但是，此查询应始终以区分大小写的方式运行，并且在可能的情况下，请勿更改排序规则区域性和其他属性。

例如，如果一个数据库恰巧正在使用SQL_Latin1_General_CP1_CI_AS（CI在此表示不区分大小写），我想使用SQL_Latin1_General_CP1_CS_AS（对于区分大小写的CS）。

简单查询示例：

DECLARE @Title nvarchar(2) = 'qQ'

--Case insensitive (following DB collation)
SELECT REPLACE(@Title, 'q', 'o') --Result: 'oo'

--Case sensitive, but fixed to a collation
SELECT REPLACE(@Title COLLATE SQL_Latin1_General_CP1_CS_AS, 'q', 'o') --Result: 'oQ'

在查询中修复这样的排序规则可能会在迁移代码或在以后更改数据库排序规则时引起问题。

是否有内置函数来获取当前排序规则的区分大小写的版本，或者可以用于此目的的变通方法？

Answer 1

排序规则不一定由数据库默认值确定：也可以为每个字符串字段设置排序规则。

不，除了使用Dynamic SQL将COLLATE子句写入查询之外，我从未见过（并且已经寻找）进行动态归类的方法。或者，如果您需要考虑的选项数量非常少，则可以尝试以下操作：

SELECT ...
FROM   ...
WHERE (@CaseSensitive = 1 AND [Field] LIKE N'%' + @Name + N'%' COLLATE Something_CS_AS)
OR (@CaseSensitive = 0 AND [Field] LIKE N'%' + @Name + N'%')

同样，区分大小写（甚至重音，假名或宽度）和不区分大小写之间没有直接等价的关系。大多数情况下，不区分大小写的排序规则都有区分大小写的对应项，但有15种排序规则仅区分大小写：

;WITH CaseS AS
(
  SELECT [name]
  FROM   sys.fn_helpcollations()
  WHERE  [name] LIKE N'%[_]cs[_]%'
)
SELECT CaseI.*
FROM   sys.fn_helpcollations() CaseI
LEFT JOIN CaseS
       ON CaseI.name = REPLACE(CaseS.[name], N'_CS_', N'_CI_')
WHERE  CaseI.[name] LIKE N'%[_]ci[_]%'
AND    CaseS.[name] IS NULL;

返回值：

name                                  description
SQL_1xCompat_CP850_CI_AS              ...
SQL_AltDiction_CP850_CI_AI            ...
SQL_AltDiction_Pref_CP850_CI_AS       ...
SQL_Danish_Pref_CP1_CI_AS             ...
SQL_Icelandic_Pref_CP1_CI_AS          ...
SQL_Latin1_General_CP1_CI_AI          ...
SQL_Latin1_General_CP1253_CI_AI       ...
SQL_Latin1_General_CP437_CI_AI        ...
SQL_Latin1_General_CP850_CI_AI        ...
SQL_Latin1_General_Pref_CP1_CI_AS     ...
SQL_Latin1_General_Pref_CP437_CI_AS   ...
SQL_Latin1_General_Pref_CP850_CI_AS   ...
SQL_Scandinavian_Pref_CP850_CI_AS     ...
SQL_SwedishPhone_Pref_CP1_CI_AS       ...
SQL_SwedishStd_Pref_CP1_CI_AS         ...

在查询中修复这样的排序规则可能会在迁移代码时引起问题，


为什么？您打算将代码迁移到哪里？如果使用另一个RDBMS，则您已经需要应对数据类型差异，SQL方言差异，“最佳实践”差异等。那么为什么还要担心排序规则呢？除非您确定要迁移到另一个RDBMS，否则应通过最大程度地使用当前平台来使系统尽其所能，而不是由于以下原因而处于非最佳状态：仅使用最低注释分母功能。


或在以后更改数据库排序规则。


你为什么要这样做？同样，任何具有显式COLLATION设置的字符串字段都不受数据库默认值的影响。



如果您要寻找对等值的严格区分大小写（以及包括Accent等在内的所有内容）的敏感性（我们不是在谈论范围搜索或排序），则可以使用Binary排序规则（即以 _BIN或 _BIN2结尾的二进制排序规则） ）。请记住，二进制排序规则可能不是您期望的排序方式，因为它们不是基于“字典”的排序方式，至少不是针对在所有语言中表现相同的单个二进制排序规则而言。它们也不会在语言之间进行对等（例如，将“ a”等同于带有重音符号的“ a”）。

自从最初发布此答案以来，我发现上面的段落实际上是个坏建议。如果目标是区分大小写，请不要使用二进制排序规则。太严格了，在许多情况下不会给出准确的结果。有关详细信息和示例，请参见： No, Binary Collations are not Case-Sensitive。

另外，请不要使用仅以 _BIN结尾的二进制排序规则，因为它们自SQL Server 2005发布以来就已经过时，并且仅在需要与也使用 _BIN排序规则的另一个系统保持向后兼容性时才使用。如果需要二进制排序规则，请使用以 _BIN2结尾的。有关详细信息和示例，请参见： Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)。



更新

我能够提出一个函数来获取传入的排序规则的区分大小写的版本（如果存在）。但是，此功能仅有助于创建正确的动态SQL。它不能在查询中内联使用以动态设置COLLATE子句（主要是因为无法做到这一点）。有两个参数：


@CollationName-如果您将此密码传递进来，则将返回该密码的区分大小写的版本（如果存在）。 @DatabaseName参数将被忽略。
@DatabaseName-如果您不知道确切的排序规则，请将 @CollationName保留为 NULL并将其传递进来，它将查找该数据库的默认排序规则。
如果两个参数均为 NULL，则它将查找该函数所在的数据库的默认排序规则。
如果传入或查找的排序规则已经区分大小写，则将返回该名称
待办事项（有时间时）：查找没有默认数据库的服务器默认排序规则（它们的默认排序规则名称为 NULL）


该函数有两个版本：第一个是TVF（因为它们更快）和Scalar UDF（因为它们有时更易于交互）。

表值函数：

USE [Test];
SET ANSI_NULLS ON;

IF (OBJECT_ID(N'dbo.GetCaseSensitiveCollation') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.GetCaseSensitiveCollation;
END;

GO
CREATE FUNCTION dbo.GetCaseSensitiveCollation
(
  @CollationName sysname,
  @DatabaseName sysname
)
RETURNS TABLE
--WITH SCHEMABINDING
--     Cannot schema bind table valued function 'dbo.GetCaseSensitiveCollation'
--     because it references system object 'sys.fn_helpcollations'.
AS RETURN

  WITH collation(name) AS
  (
    SELECT CONVERT(sysname, COALESCE(@CollationName,
                DATABASEPROPERTYEX(COALESCE(@DatabaseName, DB_NAME()), 'Collation')))
  )
  SELECT col.[name]
  FROM   sys.fn_helpcollations() col
  CROSS JOIN collation
  WHERE  col.[name] = CASE WHEN collation.[name] LIKE N'%[_]CS[_]%' 
                               THEN collation.[name]
                           ELSE REPLACE(collation.[name], N'_CI_', N'_CS_')
                      END;
GO

例子：

-- Get CS Collation for the specified Collation
SELECT [name] AS [BySpecificCollation]
FROM dbo.GetCaseSensitiveCollation(N'Indic_General_100_CI_AS_KS_WS', NULL);

-- Get CS Collation based on database default for the specified database
SELECT [name] AS [ByDefaultCollationForDB]
FROM dbo.GetCaseSensitiveCollation(NULL, N'msdb');

-- Get CS Collation based on database default for database that the function exists in
SELECT [name] AS [CurrentDB]
FROM Test.dbo.GetCaseSensitiveCollation(NULL, NULL);

-- Get CS Collation based on database default for the current database
USE [ReportServer];
SELECT [name] AS [CurrentDB]
FROM Test.dbo.GetCaseSensitiveCollation(NULL, DB_NAME());

标量用户定义函数：

USE [Test];
SET ANSI_NULLS ON;

IF (OBJECT_ID(N'dbo.GetCaseSensitiveCollation2') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.GetCaseSensitiveCollation2;
END;
GO
CREATE FUNCTION dbo.GetCaseSensitiveCollation2
(
  @CollationName sysname,
  @DatabaseName sysname
)
RETURNS sysname
--WITH SCHEMABINDING
--     Cannot schema bind table valued function 'dbo.GetCaseSensitiveCollation2'
--     because it references system object 'sys.fn_helpcollations'.
AS
BEGIN
  DECLARE @NewCollationName sysname;

  ;WITH collation(name) AS
  (
    SELECT CONVERT(sysname, COALESCE(@CollationName,
                DATABASEPROPERTYEX(COALESCE(@DatabaseName, DB_NAME()), 'Collation')))
  )
  SELECT @NewCollationName = col.[name]
  FROM   sys.fn_helpcollations() col
  CROSS JOIN collation
  WHERE  col.[name] = CASE WHEN collation.[name] LIKE N'%[_]CS[_]%'
                                THEN collation.[name]
                           ELSE REPLACE(collation.[name], N'_CI_', N'_CS_')
                      END;

  RETURN @NewCollationName;
END;
GO

例子：

/* Get CS Collation for the specified Collation */
SELECT dbo.GetCaseSensitiveCollation2(N'Indic_General_100_CI_AS_KS_WS', NULL)
                 AS [BySpecificCollation];
-- Indic_General_100_CS_AS_KS_WS

/* Get CS Collation based on database default for the specified database */
SELECT dbo.GetCaseSensitiveCollation2(NULL, N'msdb') AS [ByDefaultCollationForDB];
-- SQL_Latin1_General_CP1_CS_AS

/* Get CS Collation based on database default for the current database */
USE [ReportServer];
SELECT Test.dbo.GetCaseSensitiveCollation2(NULL, DB_NAME()) AS [CurrentDB];
-- Latin1_General_CS_AS_KS_WS

/* Get CS Collation based on database default for database where the function exists */
SELECT Test.dbo.GetCaseSensitiveCollation2(NULL, NULL) AS [DBthatFunctionExistsIn];
-- SQL_Latin1_General_CP1_CS_AS