更改SQL Server 2012数据库的排序规则 [英] Changing the collation of a SQL Server 2012 database

查看：92
本文介绍了更改SQL Server 2012数据库的排序规则的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

更改排序规则



我需要从 Latin1_General_CI_AS更改特定服务器上我们数据库之一的排序规则到 SQL_Latin1_General_CP1_CI_AI ，以便与我们的其他数据库匹配。





问题



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


ALTER DATABASE失败。数据库 XxxxxXxxxxx的默认排序规则不能设置为SQL_Latin1_General_CP1_CI_AI。 （Microsoft SQL Server，错误：5075）


我的研究



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

例如：数据库排序规则更改的问题（SQL Server 2008）



显然，这是一项艰巨的任务，尤其是因为必须保留主外键关系，并且我们的数据库非常大（超过一千万个数据行）。



我的问题



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



或者，是否有一些工具或脚本可以可靠地自动化此过程？

解决方案

以下内容在SQL Server 2012上对我有用：

  ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI ;

链接问题中接受的答案并不完全正确，至少对于SQL Server 2012而言并非如此。说：


啊，这是SQL Server中最严重的问题之一：创建对象后就不能更改排序规则（这是对于表和数据库都是如此...）。


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


在对数据库应用其他排序规则之前，请确保满足以下条件：


  1. 您是当前使用数据库的唯一人。


  2. 没有受模式约束的对象取决于数据库的排序规则。



    如果存在以下依赖于数据库排序规则的对象在数据库中，ALTER DATABASE database_name COLLATE语句将失败。 SQL Server将为阻止ALTER操作的每个对象返回一条错误消息：




    • 用SCHEMABINDING创建的用户定义函数和视图。


    • 计算列。


    • 检查约束。


    • 表值函数返回带有字符列的表，这些字符列具有从默认数据库排序规则继承的排序规则。




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




  • 放下它们

  • 更改排序规则

  • ，然后重新添加它们



但是，已更改为数据库的默认排序规则。用户表（即非系统表）中任何现有列的归类仍将具有原始归类。如果要使用现有的字符串列- CHAR ， VARCHAR ， NCHAR ， NVARCHAR 和已弃用的 TEXT 和 NTEXT -要采用新的归类，您需要分别更改每个列。并且，如果在这些列上定义了任何索引，则需要首先删除这些索引（禁用是不够的），并在 ALTER COLUMN 之后创建（其他依赖项）这样可以防止 ALTER COLUMN 已被删除，以使 ALTER DATABASE 起作用）。下面的示例说明了这种行为：



测试设置 

  USE [tempdb]; 
开启NOCOUNT； 
 
创建表dbo.ChangeCollat​​ionParent 
（
 [ChangeCollat​​ionParentID] INT非空IDENTITY（1，1）
 CONSTRAINT [PK_ChangeCollat​​ionParent]主键，
 ExtendedASCIIString VARCHAR（50）COLLATE Latin1_General_CI_AS NULL，
 UnicodeString NVARCHAR（50）COLLATE Latin1_General_CI_AS NULL 
）; 
 
创建表dbo.ChangeCollat​​ionChild 
（
 [ChangeCollat​​ionChildID] INT非空IDENTITY（1，1）
 CONSTRAINT [PK_ChangeCollat​​ionChild]主键，
 [ ChangeCollat​​ionParentID] INT空
 CONSTRAINT [FK_ChangeCollat​​ionChild_ChangeCollat​​ionParent]外键
参考dbo.ChangeCollat​​ionParent（[ChangeCollat​​ionParentID]），
 ExtendedASCIIString VARCHAR（50）COLLATE Latin1_General_CI_AS NULL（50），$ b $ String COLLATE Latin1_General_CI_AS NULL 
）; 
 
插入dbo.ChangeCollat​​ionParent（[ExtendedASCIIString]，[UnicodeString]）
值（'test1'+ CHAR（200），N'test1'+ NCHAR（200））; 
插入dbo.ChangeCollat​​ionParent（[ExtendedASCIIString]，[UnicodeString]）
值（'test2'+ CHAR（170），N'test2'+ NCHAR（170））; 
 
 
插入dbo.ChangeCollat​​ionChild 
（[ChangeCollat​​ionParentID]，[ExtendedASCIIString]，[UnicodeString]）
值（1，'testA'+ CHAR（200） ，N'testA'+ NCHAR（200））; 
插入dbo.ChangeCollat​​ionChild 
（[ChangeCollat​​ionParentID]，[ExtendedASCIIString]，[UnicodeString]）
值（1，'testB'+ CHAR（170），N'testB'+ NCHAR（ 170））； 
 
 SELECT * FROM dbo.ChangeCollat​​ionParent; 
 SELECT * FROM dbo.ChangeCollat​​ionChild;

测试1：更改列排序规则，没有依赖项 

  ALTER TABLE dbo.ChangeCollat​​ionParent 
 ALTER COLUMN [ExtendedASCIIString] VARCHAR（50）COLLATE SQL_Latin1_General_CP1_CI_AI NULL; 
 ALTER TABLE dbo.ChangeCollat​​ionParent 
 ALTER COLUMN [UnicodeString] NVARCHAR（50）COLLATE SQL_Latin1_General_CP1_CI_AI NULL; 
 
 ALTER TABLE dbo.ChangeCollat​​ionChild 
 ALTER COLUMN [ExtendedASCIIString] VARCHAR（50）COLLATE SQL_Latin1_General_CP1_CI_AI NULL; 
 ALTER TABLE dbo.ChangeCollat​​ionChild 
 ALTER COLUMN [UnicodeString] NVARCHAR（50）COLLATE SQL_Latin1_General_CP1_CI_AI NULL; 
 
 SELECT * FROM dbo.ChangeCollat​​ionParent; 
 SELECT * FROM dbo.ChangeCollat​​ionChild;

上述 ALTER COLUMN 语句已成功完成。



测试2：更改具有依存关系的列排序 

 -首先，创建索引：
创建NONCLUSTERED索引[IX_ChangeCollat​​ionParent_ExtendedASCIIString] 
在dbo.ChangeCollat​​ionParent（[ExtendedASCIIString] ASC）上； 
 
-接下来，将归类更改回原始设置：
 ALTER TABLE dbo.ChangeCollat​​ionParent 
 ALTER COLUMN [ExtendedASCIIString] VARCHAR（50）COLLATE Latin1_General_CI_AS NULL;

这一次，收到了 ALTER COLUMN 语句以下错误：


消息5074，级别16，状态1，行60

索引'IX_ChangeCollat​​ionParent_ExtendedASCIIString'为依赖于列'ExtendedASCIIString'。

消息4922，级别16，状态9，行60

ALTER TABLE ALTER COLUMN ExtendedASCIIString失败，因为一个或多个对象访问此列。


ALSO ，请注意，数据库范围内的系统目录视图中某些字符串列的排序规则（例如 sys.objects ， sys.columns ， sys.indexes 等）将更改为新的归类。如果您的代码对任何这些字符串列都具有JOIN（即 name ），那么您可能会开始收到归类不匹配错误，直到在用户表中的连接列上更改归类为止。



更新：



如果更改整个实例的排序规则，欲望或一种选择，那么有一种更简单的方法可以绕过所有这些限制。它没有文档记录，因此不受支持（因此，如果无法使用，Microsoft不会提供帮助）。但是，它将在所有级别更改排序规则：实例，所有数据库以及所有用户表中的所有字符串列。它这样做，并通过简单地更新表的元数据等来具有新的归类，从而避免了所有典型的限制。然后，它删除并重新创建具有字符串列的所有索引。此方法也有一些细微之处可能会产生影响，但可以修复。此方法是 sqlservr.exe 的 -q 命令行开关。我在以下帖子中记录了所有行为，包括通过进行如此广泛的排序规则更改来列出所有可能受影响的区域：



更改实例，数据库和所有用户数据库中所有列的排序规则：可能会出错吗？


Alter Collation

I need to change the collation of one of our databases on a particular server from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AI so that it matches the rest of our databases.

The Problem

However, when I attempt to do this, I get the following error:

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

My Research

My googling on the topic has revealed a number of articles which indicate that I need to export all the data, drop the database, re-create it with the correct collation, then re-import the data.

For example: Problem with database collation change (SQL Server 2008)

Obviously this is a significant task, especially since primary-foreign key relationships must be preserved, and our database is quite large (over ten million data rows).

My Question

Is there a way to change the collation of an existing SQL Server 2012 database which does not require exporting and re-importing all the data?

Alternatively, is there some tool or script(s) which can automate this process in a reliable manner?

解决方案

The following works for me on SQL Server 2012:

ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI;

The accepted answer in the linked question is not entirely correct, at least not for SQL Server 2012. It says:

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...).

But I was just able to change the default collation and I have tables that are populated. The MSDN page for ALTER DATABASE states in the "Remarks" section, under "Changing the Database Collation":

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.

So, I would suggest making sure that the database is in Single-User mode, and that if you have any of those four items, that you:

  • drop them
  • change the collation
  • and then re-add them

BUT, at that point all that has been changed is the Database's default Collation. The Collation of any existing columns in user tables (i.e. non-system tables) will still have the original Collation. If you want existing string columns -- CHAR, VARCHAR, NCHAR, NVARCHAR, and the deprecated TEXT and NTEXT -- to take on the new Collation, you need to change each of those columns individually. And, if there are any indexes defined on those columns, then those indexes will need to be dropped first (disabling is not enough) and created again after the ALTER COLUMN (other dependencies that would prevent the ALTER COLUMN would have already been dropped in order to get the ALTER DATABASE to work). The example below illustrates this behavior:

Test Setup

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;

Test 1: Change column Collation with no dependencies

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;

The ALTER COLUMN statements above complete successfully.

Test 2: Change column Collation with dependencies

-- 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;

This time, the ALTER COLUMN statement received the following error:

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.

ALSO, please be aware that the Collation of some string columns in database-scoped system catalog views (e.g. sys.objects, sys.columns, sys.indexes, etc) will change to the new Collation. If your code has JOINs to any of these string columns (i.e. name), then you might start getting Collation mismatch errors until you change the Collation on the joining columns in your user tables.

UPDATE:

If changing the Collation for the entire Instance is the desire, or an option, then there is an easier method that bypasses all of these restrictions. It is undocumented and hence unsupported (so if it doesn't work, Microsoft isn't going to help). However, it changes the Collation at all levels: Instance, all Database's, and all string columns in all User Tables. It does this, and avoids all of the typical restrictions, by simply updating the meta-data of the tables, etc to have the new Collation. It then drops and recreates all indexes that have string columns. There are also a few nuances to this method that might have impact, but are fixable. This method is the -q command-line switch of sqlservr.exe. I have documented all of the behaviors, including listing all of the potentially affected areas by doing such a wide-sweeping Collation change, in the following post:

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

这篇关于更改SQL Server 2012数据库的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆