关于SQL Server 排序规则

SQL Server
目录

今天写一SQL语句时，被提示" 无法解决 equal to操作中 “SQL_Latin1_General_CP1_CI_AS"和"Chinese_PRC_CI_AS"之间的排序规则冲突”。才发现有排序规则这一说法。

后来在网上查了一上，发现排序规则不同的列彼此都不能比较，字母大小写是否敏感也受排序规则影响。看来有必要好好了解一下下了。

" 在microsoft sql server中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。"

高深的东西就不说了，举一两个例子最实在。

永久和临时改变排序规则举例：

复制一个表和部分数据，再改变其中一个表的一个字段的排序规则，然后进行比较。

--复制表结构和部分数据
SELECT TOP 2 ProvinceTitle
INTO TestDB.dbo.Province
FROM Mic.dbo.bcTir_Province
--结果排序规则
ALTER TABLE TestDB.dbo.Province
ALTER COLUMN ProvinceTitle nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS
-- 比较
SELECT b.* FROM  TestDB.dbo.Province a
INNER JOIN Mic.dbo.bcTir_Province b
ON a.ProvinceTitle  = b.ProvinceTitle  -- 这里排序规则不同

会发现有上面的警告。要解决这个问题，一种办法是像上面修改排序规则一样将规则改回来，别一种方法是临时改变排序规则，就像转换数据类型一样，只不过数据库不支持隐式转换罢了：

-- 比较
SELECT b.* FROM  TestDB.dbo.Province a
INNER JOIN Mic.dbo.bcTir_Province b
ON a.ProvinceTitle COLLATE Chinese_PRC_CI_AS = b.ProvinceTitle

在where条件比较、order by后面、以及select后面，都可以像上面那样，在列名或常量后加上COLLATE [collate_name]，以临时改变排序规则。

大小写敏感问题举例：

DECLARE @test table(col varchar(50))
INSERT INTO @test
SELECT 'Aa123' UNION ALL
SELECT 'aA123' UNION ALL
SELECT 'aa123' UNION ALL
SELECT 'AA123'

SELECT * FROM @test WHERE col = 'aa123'
SELECT * FROM @test WHERE col = 'aa123' COLLATE Chinese_PRC_CS_AS
SELECT * FROM @test WHERE col COLLATE Chinese_PRC_CS_AS = 'aa123'
SELECT * FROM @test WHERE col COLLATE Chinese_PRC_CS_AS = 'aa123' COLLATE Chinese_PRC_CS_AS
SELECT * FROM @test WHERE col COLLATE Chinese_PRC_CS_AS = 'aa123' collate database_default

第一个的结果是全部，后三个的结果都只有一个 aa123。

排序规则小结

  1. 查看系统支持的所有排序规则（里面含有排序规则名称与说明）：
SELECT * FROM ::fn_helpcollations()
  1. 当前系统的默认排序规则
SELECT   SERVERPROPERTY('Collation')
--或
sp_helpsort
  1. 查看数据库所有表字段的排序规则
SELECT c.object_id , t.name, c.name,c.collation_name
FROM sys.columns c INNER JOIN sys.tables t
      ON c. object_id = t . object_id
WHERE c . collation_name IS NOT NULL
  1. 查看当前数据库的排序规则
SELECT name,collation_name
FROM sys.databases
WHERE name = DB_NAME()
  1. 修改排序规则语法
--修改数据库默认
ALTER DATABASE dbname COLLATE [collate_name]
--修改表的某一列
ALTER TABLE tbname ALTER COLUMN colname [datetype] COLLATE [collate_name]
  1. 典型排序规则举例

Chinese_PRC_BIN 二进制顺序、用于 936（简体中文）字符集。
Chinese_PRC_CI_AS 字典顺序、 不区分大小写、用于 936（简体中文）字符集。
Chinese_PRC_CS_AS 字典顺序、 区分大小写、用于 936（简体中文）字符集。
Chinese_PRC_CI_AS_WS，可区分全角半角标点