SQL Server的定序(Collation)

使用SQL Server的朋友們應該都知道SQL Server的資料庫有一個設定叫做定序(Collation)，今天我們就來看看定序這東西是什麼，首先我們看一下Wiki上對定序的說明：

Collation is the assembly of written information into a standard order. One common type of collation is called alphabetisation, though collation is not limited to ordering letters of the alphabet. Collating lists of words or names into alphabetical order is the basis of most office filing systems, library catalogs and reference books..[Reference from here.]

這段話講得玄了點，我們看另一個比較易懂的說明：

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.[Reference from here.]

這段話就簡潔多了，簡單來說定序指的就是決定資料被排序與比對的規則，而比對的規則一般可粗分為幾大類：

定序的分類

• Case sensitivity(CS)
  簡單來說就是區分大小寫，A跟a是不同的，如果是Case Insensitive(CI)的話A在排序或者查詢時就會被視為相同，也就是查詢A，連同a也會被查詢到。
• Accent sensitivity(AS)
  代表的是腔調上的差別，a跟á、o跟ó在腔調上是相同的，那查詢時是要視為相同，如果是的話，那就是Accent Insensitive(AI)，如果不是的話就視為Accent sensitive。
• Kana Sensitivity(KS)
  日文中的片假名(Hiragana)與平假名(Katakana)如果被視為相同，那就是Kana Insensitive(KI)，反之就是Kane sensitive.。
• Width sensitivity(WS)
  當半形字與全型自被視為相同(A跟Ａ)，那就是Width Insensitive(WI)，反之就是Width sensitive。

一般當我們在創建資料庫時，我們會看到好多選項：

SQL Server 2005支援的定序就有上百種了，而後頭的BIN、CA、AI、KS、WS等等就是上頭我們提到的那些Sensitive或Insensitive了，我們看一下MSDN上更詳細的說明：

其中的BIN跟BIN2是我們前面沒提到的，這兩個定序是比較特別的，與前方提到的各種Sensitive或者Insensitive的定序是有所差異的，這種定序的執行效率比其它定序來的好，針對非unicode的資料，它是自動以ANSI Code來做為排序與比較的依據(CI、AI、KI、WI)，而針對unicode資料，它則是以Unicode做為排序與比較的依據，而一旦以Unicode作為排序依據，Latin_1_General_BIN跟Japanese_BIN這兩種定序查詢回來的資料將會一模一樣，因為當資料都是非unicode時，都以ANSI Code來處理；當資料都是unicode時，就以Unicode來處理，而也因為以上特性，目前大部分的系統也習慣將資料庫的定序設定為BIN結尾的。

以下是一些我們常見到的定序結尾，C、A、K、S類的定序是可以複合被使用的，但只有BIN類的是無法結合C、A、K、S的定序被使用，這也是因為上一段中提到的BIN類定序是ANSI或Unicode來做排序與比較的原則而來。

定序的層級

我們在SQL Server中可以在以下四個定序設定等級，我們分別可以透過SSMS或SQL Expression在伺服器、資料庫、Table的欄位上指定定序：

• Server
  安裝時，SQL Server會要求你指定所想要的預設定序，而系統資料庫與後續新建的資料庫都會以這個定序為預設值，若後續要修改可以參考(因為要re-install原諒我無法測試)：
  Setting and Changing the Server Collation
• Database
  針對某個特定資料庫設定其定序，除了使用SSMS的介面變更外，也可以透過Alter Database指令來變更：
  ALTER DATABASE
• Column
  針對某個欄位設定其定序，除了使用SSMS的介面變更外，也可以透過Alter Table的指令來變更：
  Setting and Changing the Column Collation
• Expression
  這部分的定序指定是在command透過collation的關鍵字來暫時複寫掉上面三個已經設定好的定序內容，相關用法可以參考：
  COLLATE (Transact-SQL)

這些東西其實每天我們都會遭遇到，但因為目前SQL Server的相容程度與Unicode的盛行，讓我們可以免掉不少的困擾，連定序衝突的問題都不一定有機會遇到，不過上禮拜在客戶端到是遇到一次，所以就把在處理過程中查到的一些資料PO出來給大家參考參考囉。