sql-server - 可以设置T-SQL变量的排序规则吗？

Question

我找遍了，但找不到答案，你能设置变量的排序规则吗？根据MS documentation ，似乎只能在 SQL Azure 上实现:

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
DECLARE
{{ @local_variable [AS] data_type } [ =value [ COLLATE ] ] } [,...n]

目前我必须这样做:

DECLARE @Test nvarchar(10) = N'Crud';

IF ( @Test = N'Crud' COLLATE Latin1_General_CS_AI )
    Print N'Crud';
IF ( @Test = N'cRud' COLLATE Latin1_General_CS_AI )
    Print N'cRud';
IF ( @Test = N'crUd' COLLATE Latin1_General_CS_AI )
    Print N'crUd';
IF ( @Test = N'cruD' COLLATE Latin1_General_CS_AI )
    Print N'cruD';

当我想做的是:

DECLARE @Test nvarchar(10) = N'Crud' COLLATE Latin1_General_CS_AI;

IF ( @Test = N'Crud' )
    Print N'Crud';
IF ( @Test = N'cRud' )
    Print N'cRud';
IF ( @Test = N'crUd' )
    Print N'crUd';
IF ( @Test = N'cruD' )
    Print N'cruD';

我猜答案是否定的，但我想确认一下，至少，其他需要此信息的人会得到明确的答案。

非常感谢。

Answer 1

嗯，你猜对了。
在大多数 SQL Server 系统中(即不包括 Azure SQL 数据仓库和并行数据仓库)可以在四个级别上设置排序规则:

1. The default collation of the SQL Server instance :

   The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases.

2. The default collation of a specific database :

   You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using SQL Server Management Studio. If you do not specify a collation, the database is assigned the default collation of the instance of SQL Server.

3. 您可以为表格的列设置排序规则:

   You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.

4. 您可以使用Collate为特定表达式设置排序规则。子句:

   You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column.

所以，是的，除了 Azure SQL 数据仓库和并行数据仓库之外，您无法在本地标量变量上设置排序规则。