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，如需轉載請自行聯系原作者