单独修改某个数据库排序规则实践

单独修改某个数据库排序规则实践

 

背景

最近同事在做ms sql server 2012 阻塞等数据收集自动推送到PG的需求，在某些sql server服务器上测试发现某些字段中的中文显示出现乱码的情况，请求帮忙看看，我第一感觉觉得是不是nvarchar对应值未加N造成，实际查看发现是该库的字符的排序规则的问题，觉得有必要记录一下，供需要的人参考。当然这只是一个修改单库字符排序规则的实践，如需修改实例的字符排序规则，可以参考《数据库安装完毕之后如何修改数据库实例排序规则》。

 

测试环境

Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)

    Mar  2 2016 21:29:16

    Copyright (c) Microsoft Corporation

    Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 

操作过程

执行查询脚本表象如下

查看对应db_alert表结构

说明该表的字段以及排序规则是没有问题的。

查看该库的排序规则

发现对应库的排序规则不是【Chinese_PRC_CI_AS】

确定了问题那调整就比较简单了，修改可以UI界面操作或者直接脚本处理；

UI界面如下：

点击该库右键查看数据库属性

 

或者使用脚本如下：

USE master;
GO
ALTER DATABASE [具体的库名] COLLATE Chinese_PRC_CI_AS;

 

 

我默认选择执行脚本的方式；

消息 5030，级别 16，状态 5，第 1 行
The database could not be exclusively locked to perform the operation.
消息 5072，级别 16，状态 1，第 1 行
ALTER DATABASE failed. The default collation of database 'azure_monitor' cannot be set to Chinese_PRC_CI_AS.

 

从错误提示来看应该有其他链接在使用该库；

--kill 上述两个spid
kill 93
kill 96

 

重新再执行调整排序规则的脚本

消息 5075，级别 16，状态 1，第 1 行
The column 'monitor_block_header.capture_day' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
消息 5075，级别 16，状态 1，第 1 行
The column 'Monitor_blocking.blocking_date' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
消息 5075，级别 16，状态 1，第 1 行
The column 'monitor_deadlock.capture_day' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
消息 5075，级别 16，状态 1，第 1 行
The column 'monitor_deadlock.lock_sno' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
消息 5075，级别 16，状态 1，第 1 行
The column 'monitor_long_blocking.capture_date' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
消息 5072，级别 16，状态 1，第 1 行
ALTER DATABASE failed. The default collation of database 'azure_monitor' cannot be set to Chinese_PRC_CI_AS.

 

那是该库有些字段默认是绑定现有的数据库排序规则的，查看上述错误提示对应表的字段，如下图

原来是计算列默认绑定了当前的数据库排序规则，那我们就可以先把计算列删除，改数据库的排序规则之后再增加回去；

---分别执行如下三步
----第一步 删除计算列
USE azure_monitor;
ALTER TABLE [monitor_block_header]
DROP COLUMN capture_day;
ALTER TABLE Monitor_blocking
DROP COLUMN blocking_date;
ALTER TABLE monitor_deadlock
DROP COLUMN capture_day;
ALTER TABLE monitor_deadlock
DROP COLUMN lock_sno;
ALTER TABLE monitor_long_blocking
DROP COLUMN capture_date;

---第二步 修改数据库的排序规则 ,如有链接在联该库，需提前Kill
--SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID('azure_monitor')
--KILL 57
--KILL 96
USE master;
GO
ALTER DATABASE azure_monitor COLLATE Chinese_PRC_CI_AS;


----第三步 新增删除的计算列
USE azure_monitor;
ALTER TABLE [dbo].[monitor_block_header]
ADD capture_day AS (CONVERT([VARCHAR](10), [capture_time], (121)));
ALTER TABLE [dbo].[Monitor_blocking]
ADD blocking_date AS (CONVERT([VARCHAR](100), [time], (23)));
ALTER TABLE [dbo].[monitor_deadlock]
ADD capture_day AS (CONVERT([VARCHAR](12), [Capture_date], (112)));
ALTER TABLE [dbo].[monitor_deadlock]
ADD lock_sno AS ([dbo].[max_row]([lock_id]));
ALTER TABLE [dbo].[monitor_long_blocking]
ADD capture_date AS (CONVERT([VARCHAR](10), [capture_time], (121)));

 

再执行之前的查询语句

 

总结

如有些对象已默认绑定了数据库的排序规则，需提前删除，调整完数据库排序规则之后，再补建回即可。

参考

ALTER DATABASE