正确设置与使用SQL Server的字符集（Collation，即排序规则）

瓜瓜2uuq7332fe 2018-03-05

展开全文

正确认识SQL Server的字符集

SQL Server作为一个国际化产品，支持多语言环境。在SQL Server中，字符集被称为排序规则（即Collation）。排序规则不仅影响记录行的sort顺序，还影响中文显示是否乱码等。在SQL Server中，排序规则可在3处地方设置：

服务器级别 =>instances
db库级别 =>databases
表列级别 =>columns

上图所示为SQL Server默认情况下对Collation继承的方式。

即在SQL Server软件安装时我们可设置服务器级别的排序规则，也就是instances的排序规则。如下所示是使用T-SQL查询当前instances的排序规则信息：

--查询当前SQL Server服务器的排序规则
SELECT SERVERPROPERTY(N'Collation')
--------------------------------------------------
Chinese_PRC_CI_AS1
2
3
4

当然你也可以使用SSMS查看服务器属性：
服务器排序规则

若在创建databases时我们未指定排序规则，databases则会使用instances的排序规则。如下所示是通过sql代码查询或修改SQL Server中databases的排序规则信息：

--在创建database时指定排序规则：SQL_Latin1_General_CP1_CI_AS
USE master;
GO

CREATE DATABASE mydb
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

--通过目录视图sys.databases查询databases的排序规则
SELECT
    name,
    collation_name
FROM sys.databases
WHERE name = N'mydb';
-------------------------------------------------
mydb    SQL_Latin1_General_CP1_CI_AS

--修改现有databases的排序规则
ALTER DATABASE mydb
COLLATE Chinese_PRC_CI_AS1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

而表中的列（columns）默认情况是继承databases的排序规则（除非在创建表时对列的排序规则进行指定），我们可通过目录视图sys.columns查询表中columns的排序规则信息。

这里需注意：SQL Server的排序规则只影响字符型的列，如char, varchar, text, nchar, nvarchar, ntext，因此目录视图sys.columns中非字符型的排序规则显示为NULL

--注意：只有字符型的列才会显示排序规则信息，非字符型的显示为NULL
SELECT name, collation_name
FROM sys.columns
where collation_name is NOT NULL1
2
3
4

选择合适的SQL Server字符集

在安装SQL Server时，你可能困惑应该选择哪种字符集，SQL Server或者Windows的。官方推荐使用SQL Server的字符集，而非Windows的字符集。原因是，SQL Server字符集是基于Windows衍生出来的，同时保证SQL Server版本间的兼容性, 如SQL Server 2014可使用的字符集比2008多

--查看当前SQL Server支持的排序规则
SELECT * from ::fn_helpcollations()1
2

注意字符集的名字缩写与对应的的描述，如CI表示不区分大小写、
支持的排序规则

错误使用SQL Server的字符集

我们不难理解：只需保持SQL Server中3处字符集设置的地方：instances、databases、columns设置一致即是正确的使用方式。

那么当SQL Server中instances与databases对排序规则设置不一致时，将直接导致临时表#或##不能正常使用（临时表的列默认继承tempdb的排序规则，而tempdb则继承了instances的排序规则）。

/*
注意：
 这里mydb的字符集是SQL_Latin1_General_CP1_CI_AS,
 而instance的字符集是Chinese_PRC_CI_AS
*/
USE mydb;
GO

--在mydb中创建一张表collation_test
CREATE TABLE collation_test (hyper varchar(10));
GO

--创建临时表collation_temp
CREATE TABLE #collation_temp (hyper varchar(10));
GO1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

连接查询上述临时表的内容时，将出现如下报错信息：

--查询报错
SELECT  *
FROM collation_test l
LEFT JOIN #collation_temp c
    ON l.hyper = c.hyper;
--------------------------------------------
Msg 468, Level 16, State 9, Line 4
无法解决 equal to 运算中 "Chinese_PRC_CI_AS" 和 "SQL_Latin1_General_CP1_CI_AS" 之间的排序规则冲突。1
2
3
4
5
6
7
8

其根本原因是由于表collation_test与临时表#collation_temp中列的排序规则不一致。虽然可以通过以下2种方式继续使用临时表，但并不推荐。如下通过指定select表中列的排序规则，继续使用上述两张表。

--方式1：
--注意指定表collation_test使用排序规则COLLATE Chinese_PRC_CI_AS
SELECT  *
FROM collation_test l 
LEFT JOIN #collation_temp c 
    ON l.hyper COLLATE Chinese_PRC_CI_AS = c.hyper
------------------------------------------------------- 1
2
3
4
5
6
7

第二种解决方法则是在创建表时指定列的排序规则

--方式2
USE mydb;
GO

--注意指定了列的排序规则：COLLATE Chinese_PRC_CI_AS
CREATE TABLE collation_Wang
(hyper varchar(10) COLLATE Chinese_PRC_CI_AS);

--保持列的排序规则一致即可正常使用临时表#collation_temp
SELECT  *
FROM collation_wang w
LEFT JOIN #collation_temp c 
    ON w.hyper = c.hyper
-------------------------------------------------------1
2
3
4
5
6
7
8
9
10
11
12
13
14

同时instances的排序规则设置会影响SQL Server数据的导入导出功能。

通常我们遇到的另一个问题是：通过SSMS（即SQL Server Management Studio）插入（insert）的中文，在查询时显示乱码（即问号？）。

--在上述表collation_test插入中文
INSERT INTO collation_test VALUES ('东') 

--查询表collation_test的记录
select * from collation_test1
2
3
4
5

查询显示乱码：
中文乱码

这当然是由于表collation_test上hyper列的字符集设置不正确所导致的。但若你有幸在表上使用了nvarchar等类型，那么当出现上述乱码时，也许你还可以使用如下临时方式补救：

/* 
注意：
    数据库mydb依旧是使用错误的排序规则：SQL_Latin1_General_CP1_CI_AS，
    但是表collation_nvarchar使用了nvarchar类型，而非varchar
 */
USE mydb;
GO
CREATE TABLE collation_nvarchar (hyper nvarchar(10));
GO

--临时处理方式
INSERT INTO collation_nvarchar VALUES (N'东'); 

--错误插入方式
INSERT INTO collation_nvarchar VALUES ('东'); 

--查询表collation_nvarchar的记录
select * from  collation_nvarchar1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

nvarchar

综上述，我们应尽可能的正确设置SQL Server排序规则：
1. 正确的设置SQL Server排序规则，保持instances、databases、columns中3处排序规则一致，推荐使用Chinese_PRC_CI_AS
2. 尽可能使用nvarchar等Unicode类型，而非varchar类型