设置或更改列排序规则

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

可以通过为表中的特定列指定不同的排序规则，并使用以下方法之一，替代 char、varchar、text、nchar、nvarchar 和 ntext 类型数据的数据库排序规则：

  • CREATE TABLE 和 ALTER TABLE 的 COLLATE 子句，如下例所示。

    • 就地转换。 请考虑下面定义的一个现有表：

      -- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);

-- VARCHAR column is encoded the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);

      要将该列原位转换为使用 UTF-8，请运行一条 ALTER COLUMN 语句，在该语句中设置所需的数据类型和启用 UTF-8 的排序规则：

      ALTER TABLE dbo.MyTable 
ALTER COLUMN CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8

      这种方法易于实现，然而它可能是一种阻塞操作，这对于大型表和高负载应用程序来说可能会成为一个问题。

    • 复制和替换。 请考虑下面定义的一个现有表：

      -- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);
GO

-- VARCHAR column is encoded using the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
GO

      要将列转换为使用 UTF-8，请将数据复制到一个新表（该表的目标列应已设置为所需的数据类型和支持 UTF-8 的排序规则），然后替换旧表：

      CREATE TABLE dbo.MyTableNew (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
GO
INSERT INTO dbo.MyTableNew 
SELECT * FROM dbo.MyTable;
GO
DROP TABLE dbo.MyTable;
GO
EXEC sp_rename 'dbo.MyTableNew', 'dbo.MyTable';
GO

      此方法比就地转换要快得多，但处理包含许多依赖项（FK、PK、触发器、DF）的复杂架构和同步表尾（如果数据库正在使用）需要进行更多规划。

    有关详细信息，请参阅 排序规则和 Unicode 支持。

  • SQL Server Management Studio。 有关详细信息，请参阅修改列（数据库引擎）。

  • 在 SQL Server 管理对象 (SMO) 中使用 Column.Collation 属性。

如果某列当前被下列任一项引用，则无法更改该列的排序规则：

  • 计算列
  • 索引
  • 分发统计信息，可以自动生成，也可以通过 CREATE STATISTICS 语句生成
  • CHECK 约束
  • 外键约束

使用 tempdb 时，COLLATE 子句包含 database_default 选项，用于指定临时表中的列对该连接使用当前用户数据库的默认排序规则，而不是 tempdb 的排序规则。

排序规则和文本列

如果 text 列的排序规则不同于数据库默认排序规则的代码页，则可以在该列中插入值或更新该列中的值。 SQL Server 会隐式将这些值转换为该列的排序规则。

排序规则和 tempdb

每次启动 SQL Server 时，都会创建 tempdb 数据库，并且该数据库与 model 数据库具有相同的默认排序规则。 这通常与实例的默认排序规则相同。 如果为创建的用户数据库指定的默认排序规则与 model的排序规则不同，则该用户数据库与 tempdb的默认排序规则也不同。 所有临时存储过程或临时表都创建和存储在 tempdb中。 这意味着临时表中的所有隐式列以及临时存储过程中的所有强制默认常量、变量和参数都具有与可比较对象（在永久表和存储过程中创建）不同的排序规则。

这将导致用户定义的数据库和系统数据库对象之间的排序规则出现互相不匹配的问题。 例如，SQL Server 的一个实例使用 Latin1_General_CS_AS 排序规则，并执行以下语句：

CREATE DATABASE TestDB COLLATE Estonian_CS_AS;  
USE TestDB;  
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );

在此系统中， tempdb 数据库使用代码页 1252 的 Latin1_General_CS_AS 排序规则， TestDB 和 TestPermTab.Col1 使用代码页 1257 的 Estonian_CS_AS 排序规则。 例如：

USE TestDB;  
GO  
-- Create a temporary table with the same column declarations  
-- as TestPermTab  
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  
INSERT INTO #TestTempTab  
         SELECT * FROM TestPermTab;  
GO

在上述示例中， tempdb 数据库使用 Latin1_General_CS_AS 排序规则， TestDB 和 TestTab.Col1 使用 Estonian_CS_AS 排序规则。 例如：

SELECT * FROM TestPermTab AS a INNER JOIN #TestTempTab on a.Col1 = #TestTempTab.Col1;

由于 tempdb 使用默认的服务器排序规则，而 TestPermTab.Col1 使用其他排序规则，因此 SQL Server 会返回如下错误：“无法解决等于运算中 'Latin1_General_CI_AS_KS_WS' 与 'Estonian_CS_AS' 之间的排序规则冲突。”

为防止出现此错误，可以使用下列方法之一：

  • 指定临时表列使用用户数据库（而不是 tempdb）的默认排序规则。 如果您的系统有此需求，这可以使临时表与多个数据库中格式相似的表配合使用。

    CREATE TABLE #TestTempTab  
   (PrimaryKey int PRIMARY KEY,  
    Col1 nchar COLLATE database_default  
   );

  • 为 #TestTempTab 列指定正确的排序规则：

    CREATE TABLE #TestTempTab  
   (PrimaryKey int PRIMARY KEY,  
    Col1 nchar COLLATE Estonian_CS_AS  
   );

另请参阅

设置或更改服务器排序规则
设置或更改数据库排序规则
排序规则和 Unicode 支持

  • Last updated on