SQL Server Collation介绍及其变更对数据的影响

What is Collation? How it will affect SQL Server database, and server?

        在将collation之前，我们首先需要知道字符是如何被存储的。在计算机中，所有数据都是用0和1这样的位来描述。一个字节有8位，因此一个字节最多可以描述256个字符。在欧美国家，比如美国，他们的文字字符主要就是26个字母加上一些特殊符号(+-*/等)，用一个字节就可以存储，一个国家使用的所有字符就是一个code page，用一个字节存储字符的code page 叫做single-byte code page。但是在亚洲的一些国家，比如中国，常用汉字有几万个，根本不能用一个字节来表示所有的汉字字符，因此需要用两个字节描述。因为两个字节有16位，最多可以描述65536个字符，足够用来描述所有汉语字符以及常用字符，这些字符也是一个code page，不过是double-byte code page，主要针对的是中国。每个国家都有一个code page来对应所使用的字符。比如欧美国家，他们使用拉丁，虽然a-z这26个字母所对应的二进制在code page中是相同的，但是在重音（'é'和'á'）方面是不相同的。所以如果code page不同，那么相同的二进制代码所表示出来的字符也可能不相同。如果数据在不同code page的计算机上传输，就需要进行code page的转换，如果接收方的code page上没有定义传输方传送过来的特定字符的二进制位，那么就会出现数据丢失。

         在讲完字符的存储形式以后，我们就可以讲什么是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一共有四个级别，分别是server-level, database-level, column-level和expression-level。

        服务器级别的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可以在create database…collate的时候指定，如果要修改database-level collations，可以通过alter database …collate来修改。一般情况是不能修改系统数据库（master等）的collations的，除非使用前面提到的修改server-level collations的方法来修改系统数据库。

        在创建或更改表时，可使用 COLLATE 子句指定每个字符串列的排序规则。当然也可以修改column-level collations。

        Expression-level是指在执行sql语句的时候指定collations，比如：

 这一条查询语句表示按照Latin1_General_CS_AI的排序规则来进行排序。Expression-level collations的一个好处就是非常灵活。

        在对两个collations级别不同的数据库的表进行连接操作的时候，会报错。这是可以通过expression-level collations来指定使用何种collations来解决问题。比如使用Collate Database_Default 则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。

Step1：

创建两张表，第一张表使用默认的collation，第二张表在stuname列上指定collation。

<a></a>

--求表连接Step2：

执行上述查询报错如下所示：

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

上述查询执行成功。

需要注意的是collation只能用在字符串类型的列上面，如果在int列上使用collate会报错。

创建数据库，查看数据的默认database collation与server collation是否一样。

在database collation为Chinese_PRC_CI_AS的数据库中插入中文，然后修改collation为Latin1_General_CS_AI，看看已保存的数据有没有发生变化。如果再次把collation改回到Chinese_PRC_CI_AS，又有什么变化

在collation为Latin1_General_CS_AI的情况下，插入中文，会有什么情况，如何解决。

总结：

collation的变更不改变数据库原先存储的数据，原来是怎么样，修改以后还是怎样，没有发生改变。

Latin1_General_CS_AI默认是的non-unicode的，所以在这个collation下插入中文变成乱码，必须在插入数据的时候指明使用unicode形式插入，也就是添加关键字“N”，而Chinese_PRC_CI_AS这个collation使用的是double-byte code page，这里面定义了所有中文字符，所以在插入数据的时候不需要指定关键字“N”。

可以往varchar数据类型的列中插入nvarchar的数据，也就是使用varcha存储unicode的数据。

本文转自xwdreamer博客园博客，原文链接：http://www.cnblogs.com/xwdreamer/archive/2012/07/11/2585993.html，如需转载请自行联系原作者