主页 / dba / 问题 / 337
Accepted
rem
Asked: 2011-01-07 01:17:30 +0800 CST

如何更改 SQL Server 排序规则

  • 772

如何更改整个服务器和特定数据库的 SQL Server 2008 R2 Express 默认排序规则？

有没有办法使用 SQL Server Management Studio 的可视化界面来做到这一点？在“服务器属性”窗口（以及相应的“数据库属性”窗口）中，此属性不可编辑。

sql-server sql-server-2008

4 个回答

  1. Best Answer

    是的。

    您可以更改 SQL Server 2008 R2 express 实例和单个数据库的默认排序规则，但这是一项复杂的任务。

    可悲的是，没有视觉选项可以通过 SSMS 来实现。

    SQL Server 2008 支持在以下级别设置排序规则：

    • 服务器

    • 数据库

    • 柱子

    • 表达

    默认安装设置由 Windows 系统区域设置决定。服务器级排序规则可以在安装过程中更改，也可以在安装前更改 Windows 系统区域设置。更多的...

    设置和更改服务器排序规则 - SQL Server 2008

    • 确保您拥有重新创建用户数据库和其中的所有对象所需的所有信息或脚本。

    • 使用 bcp Utility 等工具导出所有数据。有关详细信息，请参阅导入和导出批量数据。

    • 删除所有用户数据库。

    • 重建 master 数据库，在 setup 命令的 SQLCOLLATION 属性中指定新的排序规则

    • 创建所有数据库和其中的所有对象。

    • 导入所有数据。

    设置和更改数据库排序规则 - SQL Server 2008

    • 在创建新数据库时设置语句COLLATION中的选项。CREATE DATABASE

    • 同样，COLLATION在语句中设置选项ALTER DATABASE以更改现有数据库的排序规则。

      ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;

    设置和更改列排序规则

    • 即使在您更改数据库排序规则后，某些列排序规则仍将保持不变。在这种情况下，您必须修改各个列的排序规则。
    • 25

  2. 确保您确实想要“删除”上述答案中所述的用户数据库。您可能只想“分离”数据库。或者说真的，你什么都做不了，因为重建 master 会有效地删除任何指向用户数据库的链接。有时数据库是按所需的排序规则创建的，但服务器不是。在这种情况下，您不希望必须从备份中恢复所有用户数据库。

    • 6

  3. 我做了这样的事情并且它有效，但你必须记住指向数据类型的索引作为 text/varchar/nvarchar 必须被删除，运行脚本然后创建索引。

    USE YourDataBase
GO

DECLARE @Table_Name NVARCHAR(100)
SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns

--- if null will set to all tables

DECLARE @TempTable AS TABLE
(
ID INT IDENTITY
,TableName NVARCHAR(100)
,ColumnName NVARCHAR(100)
,TypeName NVARCHAR(100)
,Max_length INT
,Collation_Name NVARCHAR(100)
,EnterDtm DATETIME DEFAULT GETDATE()
)
DECLARE @NewCollation NVARCHAR(100)
SET @NewCollation = 'Latin1_General_CI_AS' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)
SELECT 
QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName
,all_columns.name AS ColumnName
,type_name(all_columns.user_type_id)
,all_columns.max_length
,all_columns.collation_name  
from sys.all_columns INNER JOIN sys.tables ON
tables.object_id = all_columns.object_id
AND collation_name IS NOT NULL
AND all_columns.collation_name != @NewCollation
WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)


DECLARE @TableID SMALLINT
SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)
,@Size INT

WHILE @TableID IS NOT NULL
BEGIN
    SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)
    SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)
    SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)
    SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID) 

    SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation  
    PRINT (@Query)

    SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)
END

    这是我发布的第一个答案，请原谅我的烂摊子

    • 5

  4. 导出所有数据（包括登录名、链接服务器、SQL 代理作业、DB 邮件设置等）、重建实例级数据，以及重新加载所有用户数据，是一项繁重的工作。而且，即使在所有这些之后，仍然不能保证您可以通过更新数据库的默认排序规则，ALTER DATABASE因为有几个条件会阻止操作完成（有关详细信息，请参阅文档的“更改数据库排序规则”部分ALTER DATABASE） .

    但是，有一种更简单的未记录方法。主要缺点是它不受支持。这并不是说任何事情都会出错，只是如果出现问题，微软不会帮助修复它（因为他们从不保证它会起作用）。

    我所说的方法是sqlservr.exe使用-q {new_collation_name}交换机运行。还有更多的东西，但这是基本的想法。这种方法只是简单地更新系统元数据，有利有弊，主要有：

    好处

    • 相当快
    • 绕过大多数阻止ALTER DATABASE工作的限制
    • 可能比人们多年来想出的删除和重新创建对象的任何脚本都准确得多

    缺点

    • 如果出现问题，则不受支持
    • VARCHAR数据可以更改，如果旧排序规则和新排序规则之间的代码页不同，并且存在值为 128 - 255 (0x80 - 0xFF) 的字符，并且这些字符在新代码中不作为具有相同值的相同字符存在页。因此，数据丢失的可能性是存在的，您的数据需要首先进行研究，以确保不存在这种情况。但是，这也意味着在很多情况下，只有值在 0 - 127 之间的字符没有任何危险，即使代码页发生更改也是如此。
    • 用户定义的表类型 (UDTT) 被跳过，需要手动更新。

    有关该sqlservr.exe -q方法做什么和不做什么的详细描述（包括有关排序规则在各个级别如何工作的详细信息，以及需要注意的潜在问题），请参阅我的帖子：

    更改所有用户数据库中的实例、数据库和所有列的排序规则：可能出了什么问题？

    要仅更改实例（包括系统数据库：master、model、msdb和tempdb）和一个或多个数据库（但不是所有数据库），只需分离要从此操作中排除的数据库，然后重新附加它们一旦排序规则更新完成。

    • -1

相关问题