共计 4661 个字符，预计需要花费 12 分钟才能阅读完成。
SQL Server 中 Collation 的作用是什么，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
Collation 的组成
在讲完字符的存储形式以后，我们就可以讲什么是 collation 了。Collation 描述了数据在数据库中是按照什么规则来描述字符，以及字符时如何被排序和比较的。在 SQL Server 中，Collation 由两部分组成，比如中国的一个 collation 是 Chinese_PRC_CI_AI_WS，前半部份是指的是所支持的字符集，与 code page 相对应，如 Chinese_PRC 对应的代码页是 936，在这个 code page 中定义了所有能够使用的字符。后半部 CI_AI_WS 用于表示排序规则，比如：
_CI(CS)表示是否区分字母大小写，CI 不区分，CS 区分。如果区分大小写，那么排序的时候小写字母的排在大写的前面; 如果不区分大小写，那么排序的时候视大小写字母相同。
_AI(AS) 表示是否区分重音，AI 不区分，AS 区分。如果不区分重音，那么排序的时候视“a”和“ấ”为相同字符
_KI(KS) 表示是否区分假名类型，KI 不区分，KS 区分。在日语中应用。
_WI(WS) 表示是否区分全半角，WI 不区分，WS 区分。半角是单字节，全角是双字节。
Collation 的四个级别
Collation 一共有四个级别，分别是 server-level, database-level, column-level 和 expression-level。
Server-level Collations
服务器级别的 collation 是在安装数据库实例的时候指定的，如果没有特别指定，那么就将 windows collation 作为 server-level collation。Windows collation 由操作系统中的区域语言来决定的。
因为我们选择的是 Chinese(Simplified,PRC)，那么我们默认的 server-level collations 就是：Chinese_PRC_。Server-level collation 也是系统数据库和用户数据库的默认 collation。一般情况下 server-level collation 一旦设定就不能更改，除非将所有数据库中的对象以及数据全部导出，并创建 master，再将数据导回才可完成。
Database-level collations
Database-level collations 可以在 create database…collate 的时候指定，如果要修改 database-level collations，可以通过 alter database …collate 来修改。一般情况是不能修改系统数据库 (master 等) 的 collations 的，除非使用前面提到的修改 server-level collations 的方法来修改系统数据库。
Column-level collations
在创建或更改表时，可使用 COLLATE 子句指定每个字符串列的排序规则。当然也可以修改 column-level collations。
Expression-level collations
Expression-level 是指在执行 sql 语句的时候指定 collations，比如：
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;
这一条查询语句表示按照 Latin1_General_CS_AI 的排序规则来进行排序。Expression-level collations 的一个好处就是非常灵活。
实验 1：解决 collation 不匹配导致的冲突
在对两个 collations 级别不同的数据库的表进行连接操作的时候，会报错。这是可以通过 expression-level collations 来指定使用何种 collations 来解决问题。比如使用 Collate Database_Default 则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。
Step1：
创建两张表，第一张表使用默认的 collation，第二张表在 stuname 列上指定 collation。
create table student1
(
stuid int not null,
stuname nvarchar(20) not null,
);
create table student2
(
stuid int not null,
stuname nvarchar(20) COLLATE Latin1_General_CS_AI not null,
);
– 求表连接 Step2：
select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname
执行上述查询报错如下所示：
Cannot resolve the collation conflict between Latin1_General_CS_AI and Chinese_PRC_CI_AS in the equal to operation.
然后在 expression-level 使用 Collate Database_Default
select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname Collate Database_Default
上述查询执行成功。
需要注意的是 collation 只能用在字符串类型的列上面，如果在 int 列上使用 collate 会报错。
实验 2：变更 collation 对数据库的影响。
目的 1：
创建数据库，查看数据的默认 database collation 与 server collation 是否一样。
目的 2：
在 database collation 为 Chinese_PRC_CI_AS 的数据库中插入中文，然后修改 collation 为 Latin1_General_CS_AI，看看已保存的数据有没有发生变化。如果再次把 collation 改回到 Chinese_PRC_CI_AS，又有什么变化。
目的 3：
在 collation 为 Latin1_General_CS_AI 的情况下，插入中文，会有什么情况，如何解决。
– 实验 1：测试 nvarchar 和 varchar 的存储长度
– 创建一个默认 collation 为 Chinese_PRC_CI_AS 的数据库 TESTDB3
USE TESTDB1
CREATE TABLE test
(
lastname NVARCHAR(8) NOT NULL,–nvarchar 类型，双字节存储
title VARCHAR(8) NOT NULL, –varchar 类型，单字节存储
);
insert into test values(姓名 1 , 标题 1
select * from test;
insert into test values(123456789 , 1 –String or binary data would be truncated.
insert into test values(12345678 , 1
insert into test values(1 , 12345678
insert into test values(一二三四五六七八 , 一二三四
select * from test;
– 总结：
/*
1.nvarchar(n)，按字符来存储，不论是英文字符还是中文字符。最多能够存储 n 个中文或者是英文，但是所占用的存储空间是 2n+ 2 个字节。
2.varchar(n)按字节存储，最多能够存储 n 个英文字母，存储 n / 2 个中文字符。但是所占用的存储空间是 n 个字节。
*/
– 实验 2：collation 的变更对数据的影响。
USE TESTDB1
select * from test;
–step1：修改数据库的 collation 从默认的 Chinese_PRC_CI_AS 修改为 Latin1_General_CS_AI，英语国家都是使用这个排序规则。
use master
alter database TESTDB1 collate Latin1_General_CS_AI
–step2：通过下面的语句可以查出实例中与默认 collation 不同的数据库，查找到了我们之前的 TESTDB1。
use master
SELECT
NAME AS DATABASE_NAME
, DATABASEPROPERTYEX(NAME, COLLATION) AS DBCOLLATION
, SERVERPROPERTY(COLLATION) AS SERVERCOLLATION
FROM SYS.DATABASES
WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME, COLLATION)) SERVERPROPERTY(COLLATION)
–step3：在修改完 collation 以后查看表中的数据，发现数据没有改变。
USE TESTDB1
select * from test;
–step4：在新的 collation 下面插入数据。
insert into test values(姓名 1 , 标题 1 – 插入以后发现中文都变成了乱码 ??
select * from test;
–step5：因为 lastname 是 nvarchar 类型，我们在插入的时候指定出 nvarchar。
insert into test values(N 姓名 2 , 标题 2 – 此时发现姓名没有乱码，标题不用说还是乱码??
select * from test;
–step6：试一试在 varchar 类型的字段插入 nvarchar 是数据。
insert into test values(N 姓名 3 ,N 标题 3 – 发现标题也不是乱码了。
select * from test;
————————————
–step7：将 collation 从 Latin1_General_CS_AI 改回默认的 Chinese_PRC_CI_AS。
use master
alter database TESTDB1 collate Chinese_PRC_CI_AS
–step8：在修改完 collation 以后查看表中的数据，发现数据没有改变。
USE TESTDB1
select * from test;
–step9: 插入字符，不指定 unicode 类型，查询显示的是中文，表明当前 collation 默认使用 unicode 编码。
insert into test values(姓名 4 , 标题 4
select * from test;
总结：
collation 的变更不改变数据库原先存储的数据，原来是怎么样，修改以后还是怎样，没有发生改变。
Latin1_General_CS_AI 默认是的 non-unicode 的，所以在这个 collation 下插入中文变成乱码，必须在插入数据的时候指明使用 unicode 形式插入，也就是添加关键字“N”，而 Chinese_PRC_CI_AS 这个 collation 使用的是 double-byte code page，这里面定义了所有中文字符，所以在插入数据的时候不需要指定关键字“N”。
可以往 varchar 数据类型的列中插入 nvarchar 的数据，也就是使用 varcha 存储 unicode 的数据。
关于 SQL Server 中 Collation 的作用是什么问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。
