修改SQL Server 2012的服务器排序规则,不能像改数据库排序规则那样用一条ALTER语句搞定,最稳妥的办法是备份所有业务库后,用setup.exe重建系统数据库,并在命令行里用/SQLCOLLATION参数指定新的排序规则。 下面我把完整步骤、隐藏坑和迁移场景一并讲清楚。
服务器排序规则改之前,先搞清楚你在改什么
服务器排序规则(Server Collation)控制的是系统数据库的元数据排序方式,包括master、model、msdb和tempdb,它直接决定了你新建数据库时默认的排序规则,也影响临时表、变量和游标的默认行为,很多朋友改了数据库的排序规则,却发现临时表还是老样子,就是因为服务器的排序规则没动。
sql2012的服务器排序规则是什么,查一查就明白
你可以在SSMS里右键实例,选“属性”,切到“高级”页签,最下面那行就是“服务器排序规则”,想看命令行结果,执行这条SQL:
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
常见结果是Chinese_PRC_CI_AS,代表简体中文、不区分大小写、区分重音,如果返回SQL_Latin1_General_CP1_CI_AS,说明当时装系统时选了英文环境,这种环境下建的中文排序资料,联表查询很容易报“Cannot resolve the collation conflict between”错误。
这里有个核心认知:SQL Server 2012的服务器排序规则在安装后就没有图形化界面让你直接改,微软官方也明确说,改排序规则属于“重建系统数据库”的范畴,不是改配置项。
sql2012实例级排序规则怎么改才是正确操作
“sql2012实例级排序规则怎么改”这句话经常被搜到,但答案只有一个方向:备份后重建,下面两套操作路径,任选其一。
sql2012怎么修改服务器排序规则:用安装中心重建
这个方法适合不熟悉命令行的人,全程有界面提示。
- 完整备份所有用户数据库,这一步不能省,重建master等于换了一个新实例,你的业务库虽然还在数据文件里,但没有master的元数据就是一堆“孤儿”文件。
- 脚本化所有服务器级对象,登录账号、链接服务器、SQL Agent作业,全部用“生成脚本”功能导出,右键数据库引擎实例,选择“任务”-“生成脚本”,对象类型里勾选“登录名”和“作业”。
- 插入SQL Server 2012安装光盘或挂载ISO,打开安装中心,选择“维护”-“重建系统数据库”。
- 按照向导走到“服务器配置”这一步,在“排序规则设置”里选择你想要的规则,比如
Chinese_PRC_CI_AS。 - 确认实例名称,完成重建。
整个过程大概10到15分钟,实例会重启,重建完成后,第一件事是打开SSMS,执行上面那条查询,确认排序规则已经变成你选的值。
方法B:命令行setup.exe快速重建
如果你更习惯命令行,或者对方服务器是核心机房不方便开图形界面,可以用静默模式,以管理员身份打开命令提示符,定位到安装目录:
cd C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
然后执行:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /SQLCOLLATION=Chinese_PRC_CI_AS
需要注意几个参数:
/INSTANCENAME:默认实例写MSSQLSERVER,命名实例写你的实例名。/SQLSYSADMINACCOUNTS:指定sysadmin固定服务器角色成员,至少给一个Windows账户。/SQLCOLLATION:你要换的目标排序规则。/Q:静默模式,不加反而会弹错误。
命令跑完后,查看安装日志有没有报错,再进SSMS确认结果。
修改sql2012排序规则要注意哪些隐藏坑
说实话,改排序规则本身不难,难的是改完之后一摊子事,业内专家指出,相当一部分朋友在重建完成后直接恢复库,结果发现业务全乱套,就是因为漏了几步。
用户数据库的collation不会跟着变
重建系统数据库只改master、model、msdb和tempdb的排序规则,你原来那些业务库,如果当初是在旧规则下创建的,它们的collation_name还是老值,这会造成一个奇怪的现象:服务器排序规则是新的,库里查出来还是旧的。
所以重建完成后,你需要手动把每个业务库也切到新排序规则:
ALTER DATABASE [你的数据库名] COLLATE Chinese_PRC_CI_AS;
执行这条命令时,你的业务库不会被锁定,但建议在维护窗口做,因为它要重建所有堆和索引的元数据,短时间内有IO压力。
列级别排序规则和临时表的老问题
这一步是“修改sql2012排序规则要注意哪些隐藏坑”里最容易被忽略的,数据库的排序规则改了,不代表表里每一列都改了,只有varchar、char、text这些非Unicode字符列才有排序规则属性,nvarchar、nchar等Unicode类型列本身就带Unicode排序规则,跟服务器规则无关。
我实际遇到过的情况是这样的:把库和表都改完了,结果一个存储过程里用临时表和正式表关联,又冒出来“Cannot resolve the collation conflict”,为什么?因为临时表在tempdb里,而tempdb的排序规则在重建后已经变了,正式表列的排序规则还没跟上。
所以你要查一下所有列:
SELECT
OBJECT_SCHEMA_NAME(c.object_id) AS SchemaName,
OBJECT_NAME(c.object_id) AS TableName,
c.name AS ColumnName,
c.collation_name
FROM sys.columns c
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> 'Chinese_PRC_CI_AS'
ORDER BY OBJECT_NAME(c.object_id);
返回结果里每一行,你都需要生成ALTER TABLE ... ALTER COLUMN ... COLLATE ...的脚本去改,这一步可以用SSMS的“生成更改脚本”功能,右键表,设计表,改排序规则,然后点“生成更改脚本”,比手写快得多。
改完列,别忘了索引和统计信息
改列排序规则后,索引可能失效或需要重建,你会在SSMS里看到部分索引变为“已禁用”,这时候要跑一遍:
ALTER INDEX ALL ON [表名] REBUILD;
统计信息也会过期,用UPDATE STATISTICS刷新,这条不要漏,否则查询计划会让你怀疑人生。
如何把sql2012老库迁到新实例并换排序规则
现实中,更多人的场景不是在线改,而是趁迁移服务器顺手把排序规则换掉,搜索“sql2012如何迁移到新实例并修改排序规则”的朋友,多半是要从老机器搬到新机器,同时把中文规则统一过来。
迁移前要导出哪些清单
这一步叫“迁移前清单检查”,除了正常的登录账号和作业,还需要导出:
- 所有数据库的排序规则列表,用
sys.databases查。 - 所有列级别的非默认排序规则列表,就是上面那段
sys.columns的查询结果。 - SQL Agent代理设置、操作员通知配置。
导出登录账号直接用SSMS的生成脚本比较靠谱,也可以借助SP_HELPLOGIN把结果汇总出来,重建后执行。
新实例怎么装才不留祸根
安装新实例时,在“服务器配置”页面直接选好Chinese_PRC_CI_AS,千万不要一路默认,装完之后,先把登录账号和作业恢复到新实例,再恢复用户数据库。
这里有个顺序问题:先恢复数据库再建登录,会导致数据库里的孤立用户一堆;先恢复账号,数据库还没来,账号也建不上,正确做法是:
- 建登录账号。
- 恢复数据库。
- 用系统存储过程
sp_change_users_login 'update_one'把数据库用户和登录账号重新映射。
恢复数据库后验证三个地方
恢复完成后,不要急着让业务连上来,先跑三件事:
- 查询
SERVERPROPERTY('Collation')确认服务器级已生效。 - 查询
sys.databases确认每个库的collation都是目标规则。 - 拿一条在旧环境必报排序冲突的查询,在新环境跑通。
这三步全过,才算迁移完成,如果哪一步不对,先回查步骤,别直接上线。
Q&A:sql2012修改排序规则后数据库乱码怎么办
问:sql2012修改排序规则后数据库乱码怎么办?
答:先确认乱码的数据类型,如果是nvarchar列,你看到的“乱码”多半是客户端显示问题,Unicode数据本身不因排序规则改变而损坏,如果是varchar列,数据确实可能变成乱码,因为排序规则重写后,同一串字节在中文代码页和拉丁代码页下解释出来的字符完全不一样,解决办法是把该列数据先用varbinary类型还原成原始字节流,再按正确的目标排序规则重新转换:
UPDATE 表名 SET 列名 = CONVERT(varchar(100), CONVERT(varbinary(100), 列名)) WHERE 主键范围;
这个操作要谨慎,先备份该表,如果乱码已经发生,最快的纠错路径是把排序规则改回原值,用转换函数把数据恢复成正常字符串,再重新规划修改方案。
问:sql2012排序规则冲突怎么解决最快?
答:如果只是某一条查询报冲突,不想动大结构,可以直接在JOIN条件上用COLLATE关键字指定匹配规则:
SELECT FROM A INNER JOIN B ON A.code = B.code COLLATE Chinese_PRC_CI_AS;
这是治标,对临时联表很管用,要根治,还是得找出哪个表或哪一列的排序规则不统一,按前面讲的方法把库和列的排序规则全部改成一致值。
问:重建系统数据库后,SQL Agent作业还在吗?
答:不在了,master库保存了所有服务器级对象,包括SQL Agent作业、操作员、警报、链接服务器、登录账号,重建后这些全部回到默认状态,所以重建前必须用生成脚本导出登录名和作业,重建完再逐条创建,很多朋友在这个环节翻车,导出的脚本不完整,恢复后缺少了某个关键作业,导致定时任务静默失效。
首发原创文章,作者:王坚,如若转载,请注明出处:https://idctop.com/article/806511.html


