sql-server - 更改 SQL Server 2012 数据库的排序规则

Question

更改排序规则

我需要将特定服务器上的一个数据库的排序规则从 Latin1_General_CI_AS 更改为 SQL_Latin1_General_CP1_CI_AI，以便它与我们的其余数据库相匹配。

问题

但是，当我尝试执行此操作时，出现以下错误:

ALTER DATABASE failed. The default collation of database 'XxxxxXxxxxx' cannot be set to SQL_Latin1_General_CP1_CI_AI. (Microsoft SQL Server, Error: 5075)

我的研究

我对该主题的谷歌搜索显示了许多文章，这些文章表明我需要导出所有数据，删除数据库，使用正确的排序规则重新创建它，然后重新导入数据。

例如:Problem with database collation change (SQL Server 2008)

显然这是一项重要的任务，特别是因为必须保留主外键关系，而且我们的数据库非常大(超过一千万数据行)。

我的问题

有没有一种方法可以更改现有 SQL Server 2012 数据库的排序规则，而不需要导出和重新导入所有数据？

或者，是否有一些工具或脚本可以以可靠的方式自动执行此过程？

Answer 1

以下内容适用于 SQL Server 2012:

ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI;

链接问题中接受的答案并不完全正确，至少对于 SQL Server 2012 不正确。它说:

Ahh, this is one of the worst problems in SQL Server: you cannot change the collation once an object is created (this is true both for tables and databases...).

但我只能更改默认排序规则，并且我有已填充的表。 ALTER DATABASE 的 MSDN 页面在“更改数据库排序规则”下的“备注”部分中指出:

Before you apply a different collation to a database, make sure that the following conditions are in place:

1. You are the only one currently using the database.

2. No schema-bound object depends on the collation of the database.

   If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:

   • User-defined functions and views created with SCHEMABINDING.

   • Computed columns.

   • CHECK constraints.

   • Table-valued functions that return tables with character columns with collations inherited from the default database collation.

因此，我建议确保数据库处于单用户模式，并且如果您有这四个项目中的任何一个，您:

• 丢掉它们
• 更改排序规则
• 然后重新添加它们

但是，此时所有已更改的是数据库的默认排序规则。用户表(即非系统表)中任何现有列的排序规则仍将具有原始排序规则。如果您想要现有的字符串列 - CHAR , VARCHAR , NCHAR , NVARCHAR ，以及已弃用的 TEXT和NTEXT -- 要采用新的排序规则，您需要单独更改每一列。并且，如果在这些列上定义了任何索引，则需要首先删除这些索引(禁用是不够的)并在 ALTER COLUMN 之后再次创建。 (为了让 ALTER COLUMN 正常工作，其他可能阻止 ALTER DATABASE 的依赖项已经被删除)。下面的示例说明了这种行为:

测试设置

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE dbo.ChangeCollationParent
(
  [ChangeCollationParentID] INT NOT NULL IDENTITY(1, 1)
                    CONSTRAINT [PK_ChangeCollationParent]  PRIMARY KEY,
  ExtendedASCIIString VARCHAR(50) COLLATE Latin1_General_CI_AS NULL,
  UnicodeString NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL
);

CREATE TABLE dbo.ChangeCollationChild
(
  [ChangeCollationChildID] INT NOT NULL IDENTITY(1, 1)
                    CONSTRAINT [PK_ChangeCollationChild]  PRIMARY KEY,
  [ChangeCollationParentID] INT NULL
                    CONSTRAINT [FK_ChangeCollationChild_ChangeCollationParent] FOREIGN KEY
                         REFERENCES dbo.ChangeCollationParent([ChangeCollationParentID]),
  ExtendedASCIIString VARCHAR(50) COLLATE Latin1_General_CI_AS NULL,
  UnicodeString NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL
);

INSERT INTO dbo.ChangeCollationParent ([ExtendedASCIIString], [UnicodeString])
VALUES ('test1' + CHAR(200), N'test1' + NCHAR(200));
INSERT INTO dbo.ChangeCollationParent ([ExtendedASCIIString], [UnicodeString])
VALUES ('test2' + CHAR(170), N'test2' + NCHAR(170));


INSERT INTO dbo.ChangeCollationChild
         ([ChangeCollationParentID], [ExtendedASCIIString], [UnicodeString])
VALUES (1, 'testA ' + CHAR(200), N'testA ' + NCHAR(200));
INSERT INTO dbo.ChangeCollationChild
         ([ChangeCollationParentID], [ExtendedASCIIString], [UnicodeString])
VALUES (1, 'testB ' + CHAR(170), N'testB ' + NCHAR(170));

SELECT * FROM dbo.ChangeCollationParent;
SELECT * FROM dbo.ChangeCollationChild;

测试 1:更改没有依赖关系的列排序规则

ALTER TABLE dbo.ChangeCollationParent
  ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL;
ALTER TABLE dbo.ChangeCollationParent
  ALTER COLUMN [UnicodeString] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL;

ALTER TABLE dbo.ChangeCollationChild
  ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL;
ALTER TABLE dbo.ChangeCollationChild
  ALTER COLUMN [UnicodeString] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL;

SELECT * FROM dbo.ChangeCollationParent;
SELECT * FROM dbo.ChangeCollationChild;

ALTER COLUMN上述语句成功完成。

测试 2:更改具有依赖项的列排序规则

-- First, create an index:
CREATE NONCLUSTERED INDEX [IX_ChangeCollationParent_ExtendedASCIIString]
  ON dbo.ChangeCollationParent ([ExtendedASCIIString] ASC);

-- Next, change the Collation back to the original setting:
ALTER TABLE dbo.ChangeCollationParent
  ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE Latin1_General_CI_AS NULL;

这一次，ALTER COLUMN声明收到以下错误:

Msg 5074, Level 16, State 1, Line 60
The index 'IX_ChangeCollationParent_ExtendedASCIIString' is dependent on column 'ExtendedASCIIString'.
Msg 4922, Level 16, State 9, Line 60
ALTER TABLE ALTER COLUMN ExtendedASCIIString failed because one or more objects access this column.

另外，请注意，数据库范围的系统目录 View 中某些字符串列的排序规则(例如 sys.objects 、 sys.columns 、 sys.indexes 等)将更改为新的整理。如果您的代码具有与任何这些字符串列的联接(即 name )，那么您可能会开始收到排序规则不匹配错误，直到您更改用户表中联接列的排序规则。

更新:

如果更改整个实例的排序规则是您的愿望或选择，那么有一种更简单的方法可以绕过所有这些限制。它没有文档记录，因此不受支持(所以如果它不起作用，微软不会提供帮助)。但是，它会更改所有级别的排序规则:实例、所有数据库以及所有用户表中的所有字符串列。它通过简单地更新表的元数据等来获得新的排序规则来做到这一点，并避免所有典型的限制。然后，它删除并重新创建具有字符串列的所有索引。此方法还有一些细微差别，可能会产生影响，但可以修复。这个方法就是-q sqlservr.exe 的命令行开关。我在以下帖子中记录了所有行为，包括通过进行如此广泛的排序规则更改来列出所有可能受影响的区域:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?