SQL sever 排序规则介绍

一.SQL Server 排序规则后缀含义
_BIN 指定使用向后兼容的二进制排序顺序。

_BIN2 指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。

_Stroke 按笔划排序

_CI(CS) 是否区分大小写，CI不区分，CS区分(case-insensitive/case-sensitive)

_AI(AS) 是否区分重音，AI不区分，AS区分(accent-insensitive/accent-sensitive)

_KI(KS) 是否区分假名类型，KI不区分，KS区分(kanatype-insensitive/kanatype-sensitive)

_WI(WS) 是否区分全半角， WI不区分，WS区分(width-insensitive/width-sensitive)

二.SQL Server 排序规则层级
如下图：SQL Server 的排序规则在数据库中通过 服务器级，数据库级，列级，表达式级 三级进行管理。

 

三.排序规则如何选择
通常我们在安装SQL Server 数据库时一般不会特意指定Server级的排序规则，那么SQL Server 在安装的时候是根据系统区域设置自动生成的的默认排序规则。举例：在Windows server 系统区域设置为英语（美国）时，安装数据库，因为向后兼容性的原因，默认安装的排序规则为：SQL_Latin1_General_CP1_CI_AS。

但是还是强烈建议安装数据库时将数据库的排序规则改为：Latin1_General_100_CI_AS_SC。

这两个排序规则的区别在于：

1.Latin1_General_100_CI_AS_SC 排序规则更新了对最新版的Unicode 字符集的支持，因此可以处理一些新的Unicode字符。

举例：中文中的〇，Ǹ 等字符在SQL_Latin1_General_CP1_CI_AS 中是不存在，无法识别。

2.后缀SC：Unicode 联盟为每个字符分配一个唯一的代码点，该代码点的值在 000000–10FFFF 范围内，但最常用的字符具有 000000–00FFFF 范围内，因此不带SC后缀的只能指定在 000000–00FFFF 之间，为了解决这个问题，SQL Server 2012之后增加了一个SC后缀，以表示完整的 Unicode 字符范围(000000–10FFFF)，避免遗漏。

 

四.如何修改排序规则
修改排序规则是一件相对麻烦的工作，一般上来讲不大建议修改排序规则，但如果真的需要，可以根据使用的具体场景一定要按照上文的排序规则层级由下到上来进行更改，更改时避免遗漏，可以借用一些SQL Server 自带的系统视图查找，并通过脚本循环逐一更改。

1.修改表达式排序规则
在SQL 查询时默认排序规则无法覆盖，可在SQL 查询表达式中指定排序规则进行转换查询。
2.修改列级排序规则
如果已知某一列中一定会有特殊字符，比如医院名称：（九四〇医院），也可以真针对存储有这一类数据的所有列更改排序规则；

如下实例：

ALTER TABLE [dbo].[DimEmployee]

ALTER COLUMN [EmailAddress] nvarchar(50)

COLLATE Latin1_General_100_CI_AS_SC;

注意：

1.可以通过information_schema.COLUMNS 系统视图，从而方便查询和定位需要修改的字段，避免遗漏。

2.修改前需要删除相关的所有表的主键和索引，可以通过sys.indexes 系统视图查看，修改前需要备份，修改完后再恢复。

3.修改后需要重新编译视图：sp_refreshview 'V_DimAccount'

可以通过以下脚本获取所有的视图。

select name

from sysobjects

where1=1 AND xtype='V' and status>=0

3.修改数据库级排序规则
如果希望整个数据库的排序规则进行修改，则可以通过以下代码实例实现：

ALTER DATABASE [DW] ----数据库名称

COLLATE Latin1_General_100_CI_AS_SC

 

注意：

1.修改之前需要把数据库设置为Single模式。

 

4.修改Server级排序规则
如果希望修改Server级的排序规则，会相对复杂一些。

意执行CMD时以管理员账号运行:

----1. 停止SQL Server实例服务

Net stop mssqlserver

----2. 打开“命令提示符”，转到SQL Server的安装目录---搜索关键词Setup Bootstrap

------C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017

cd "\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"-----具体目录依实际情况定

----3. 运行setup---注意实例名称的修改

Setup /QUIET /ACTION=REBUILDDATABASE /instancename=MSSQLSERVER /SQLSYSADMINACCOUNTS=[Account]/sapwd=[password]/sqlcollation=Latin1_General_100_CI_AS_SC------注意账号密码需要修改

---4. 启动SQL Server实例服务

Net start mssqlserver

注意：

更改完之后，系统数据库将分离状态，需要重新附加。
所有的系统数据库会重新生成，因此之前设置的用户，权限等都会丢失，做之前需要做好备份。
————————————————
版权声明：本文为CSDN博主「li_jerry」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/li_jerry/article/details/124845305