SQL Server 2008 R2 排序規則詳解

一、 使用排序規則

排序規則指定字串資料如何比較和排序的規則，基於特定的語言與區域標準。例如，在ORDER BY子句中，如果按升序排列的話，說英語的人會期望字串'Chiapas'出現在'Colima'之前；然而，說西班牙語的墨西哥人將期望以‘Ch’開頭的單詞出現在以'C'開頭的單詞列表的末尾。排序規則負責控制這些型別的比較與排序規則。 在 ORDER BY ASC的子句中，Latin_1 General 排序規則將‘Chiapas’排在‘Colima’之前，而Traditional_Spanish 排序規則將‘Chiapas’排在‘Colima’之後。

當為非Unicode字元資料如char,varchar,text指定排序規則時，一個特定的code page將與之關聯。例如，如果資料表中為char型別的某列定義了Latin1_General 排序規則，那麼，SQL Server將使用1252 code page解釋和顯示該列中的資料。

對於非Unicode資料，多個排序規則可以使用相同的code page。而對於純Unicode資料如nchar,nvarchar,nvachar(max),指定的排序規則則沒有與之關聯的code page。Unicode資料能夠處理大多數的通用字元。

二、 內碼表體系結構

排序規則控制SQL Server中字串的物理儲存。它指定了表示每個字元的bit排列方式，以及字元比較和排序的規則。
在計算機中，字元表示為ON/OFF的不同bit排列方式。一個位元組有8個bits，8個bits就有256種不同的ON/OFF排列。透過為每個字元分配一種bit排列方式，每個字元佔用1個位元組儲存的程式因此最多可以表示256個不同的字元。2個位元組有16個bits，16個bits就有65536中不同的ON/OFF排列方式。使用2個位元組表示1個字元的程式最多能夠表示65536個不同字元。
單位元組code pages就是將字元對映到一個位元組中可能的256種bit排列方式中的每一種的定義。Code page為大寫字元、小寫字元、數字、符號以及特殊字元如！,@,#,%等定義bit排列方式。每一種歐洲語言如德語或西班牙語都有自己的單位元組Code page。儘管用於表示從A到Z的拉丁字母表字元的bit排列方式都是相同的，但是，表示重音符號字元的bit排列方式則隨code page而不同。
對於許多語言來說，單位元組字符集不能儲存所有字元。有些亞洲語言有成千上萬個字元。因此，它們必須使用2個位元組表示一個字元。已經為許多語言定義了雙位元組字符集，以及與之相關的code page。

三、 排序規則分類

SQL Server提供了2組排序規則：Windows排序規則和SQL Server排序規則。

Windows排序規則命名

Syntax
:: = 
     CollationDesignator_
:: = 
    { CaseSensitivity_AccentSensitivity
        [ _KanatypeSensitive ] [ _WidthSensitive ]  }
  | { _BIN | _BIN2 }

Arguments
CollationDesignator

Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:

• The sorting rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
• The code page used to store non-Unicode character data.

Some examples are:

• Latin1_General or French: both use code page 1252.
• Turkish: uses code page 1254.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

KanatypeSensitive

Omitted specifies kanatype-insensitive, KS specifies kanatype-sensitive.

WidthSensitivity

Omitted specifies width-insensitive, WS specifies width-sensitive.

BIN

Specifies the backward-compatible binary sort order to be used.

BIN2

Specifies the binary sort order that uses code-point comparison semantics introduced in SQL Server 2005.

Notes：
Kanatype Sensitive

Distinguishes between the two types of Japanese kana characters: Hiragana and Katakana.
If this option is not selected, SQL Server considers Hiragana and Katakana characters to be equal for sorting purposes

Width Sensitive

Distinguishes between a single-byte character and the same character when represented as a double-byte character.
If this option is not selected, SQL Server considers the single-byte and double-byte representation of the same character to be identical for sorting purposes.

SQL Server排序規則命名

Syntax 
:: = 
     SQL_SortRules[_Pref]_CPCodepage_
::=
    _CaseSensitivity_AccentSensitivity | _BIN

Arguments
SortRules

A string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified. Examples are Latin1_General or Polish.

Pref

Specifies uppercase preference.

Codepage

Specifies a one- to four-digit number that identifies the code page used by the collation. CP1 specifies code page 1252, for all other code pages the complete code page number is specified. For example, CP1251 specifies code page 1251 and CP850 specifies code page 850.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

BIN

Specifies the binary sort order to be used.

四、 選擇排序規則

如果你的SQL Server例項的所有使用者都說同樣的語言，你應該選擇支援該語言的排序規則。比如，如果所有使用者都說法語，就選擇French排序規則。如果你的SQL Server使用者說不同的語言，你應該選擇能夠最大限度支援各種語言需要的排序規則。比如，如果使用者通常說西歐語言，就選擇Latin1_General排序規則。
當你支援說不同語言的使用者時，最重要的是為所有字元資料使用Unicode資料型別如nchar,nvarchar,nvarchar(max)。Unicode避免了非Unicode資料型別如char,varchar,text的code page轉換難題。當你為所有列使用Unicode資料型別時，排序規則仍然是至關重要的，因為它定義了Unicode字元的比較次序和排序。即使你使用Unicode資料型別儲存字元資料，你也應當選擇支援大多數使用者的排序規則，以免列或變數使用非Unicode資料型別實現。
SQL Server僅能支援底層作業系統已安裝或支援的code page。當你執行依賴於排序規則的操作時，被參考物件所使用的SQL Server排序規則必需使用執行於該計算機上的作業系統支援或安裝的code page。
Windows2000及以後的Windows系統支援SQL Server使用的所有code page。

五、 設定和改變排序規則

排序規則可以在分別在server,database,column,expression和identifier層級指定。當你安裝SQL Server例項時，你為該例項指定預設的server級排序規則。每次你建立資料庫，你可以為該資料庫指定預設的排序規則。如果你沒有指定排序規則，那麼伺服器例項的預設排序規則將作為該資料庫的預設排序規則。無論何時你定義字元型別的列、變數或引數，都可以為該物件指定排序規則。若你沒有指定，將使用資料庫的預設排序規則建立該物件。

六、 應用範例

檢視當前SQL Server Instance的排序規則設定
select serverproperty('collation') as CollationSetting
//Chinese_PRC_CI_AS

檢視SQL Server Instance支援的排序規則列表
SELECT * FROM ::fn_helpcollations()

檢視使用者資料庫的排序規則設定
select databaseproperty('jiradb', 'collation') as CollationSetting
// NULL (未指定，將應用server例項的設定)

select DATABASEPROPERTYEX('jiradb', 'collation') as CollationSetting
//Chinese_PRC_CI_AS

SELECT name, collation_name FROM sys.databases WHERE name = 'jiradb'
//Chinese_PRC_CI_AS

修改資料庫的排序規則設定
ALTER DATABASE jiradb COLLATE Latin1_General_CS_AS

修改Column的排序規則設定
CREATE TABLE MyTable
  (PrimaryKey   int PRIMARY KEY,
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL
  )
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

在查詢的ORDER BY子句中指定排序規則，覆蓋server、database或column層級的預設排序規則
USE AdventureWorks2008R2;
GO
SELECT LastName FROM Person.Person
ORDER BY LastName
COLLATE Traditional_Spanish_ci_ai ASC;
GO

忽略重音符號
select *
from dbo.Restaurants
where Name collate SQL_Latin1_General_CP1_CI_AI like 'Cafe'
//返回結果集中將包含 'Cafe' 和 'Café'。

七、 使用Unicode資料

當你僅使用字元資料和內碼表時，想要在一個資料庫中以多種語言儲存資料是難以管理的。想要為資料庫找到一種內碼表能夠儲存所有必需的語言特定的字元，同樣也是困難的。此外，當被執行各種各樣內碼表的不同客戶端讀取或更新時，難以保證這些特殊字元被正確地翻譯。支援國際化客戶端的資料庫應當總是使用Unicode資料型別而不是非Unicode資料型別。
隨著Internet的快速發展，支援更多執行不同語言和地區的客戶端計算機就顯得更加重要。為字元資料型別選擇一種能支援全世界範圍內使用者需要的所有字元的內碼表，將是困難的。
在國際化資料庫中管理字元資料的最容易的辦法是，總是使用Unicode資料型別如nchar，nvarchar和nvarchar(max),而不使用對應的非Unicode型別如char，varchar和text。
Unicode是將code points對映到字元的標準。由於它被設計用來涵蓋世界上所有語言的所有字元，因此不需要使用不同的內碼表來處理不同的字符集。SQL Server支援3.2版本的Unicode標準。
如果使用國際化資料庫的所有應用程式也使用Unicode而不是非Unicode變數，在系統中任何地方都不需要執行字元翻譯。這些客戶端將看到與所有其他客戶端同樣的資料。
SQL Server將列中的所有文字系統目錄資料儲存為Unicode資料型別。資料庫物件如表，檢視和儲存過程的名稱，都儲存在Unicode資料型別的列中。這使得應用程式能夠僅使用Unicode開發，從而幫助避免了與內碼表轉換相關的所有問題。

八、 Unicode字元的儲存以及對效能的影響

SQL Server使用UCS-2編碼方法儲存Unicode資料。在此機制下，所有Unicode字元都使用2個位元組儲存。
Unicode與非Unicode字元資料在儲存方式上的差異取決於非Unicode資料是否使用雙位元組字符集（DBCS）儲存。所有非東亞語言和泰國語以單位元組儲存非Unicode字元。因此，以Unicode儲存這些語言所佔用的空間是使用指定的非Unicode內碼表的2倍。另一方面，許多其他亞洲語言的內碼表指定以雙位元組字符集（DBCS）儲存字元。因而對這些語言而言，Unicode和非Unicode字元在儲存上幾乎沒有差異。
Unicode資料對效能的影響是以下各種因素綜合決定的：

• Unicode排序規則和非Unicode排序規則之間的差異
• 雙位元組字元與單位元組字元在排序上的差異
• 客戶端與伺服器之間的內碼表轉換

對於定義了Windows排序規則的非Unicode資料，SQL Server使用Unicode排序規則執行字串比較。一般地，定義了Windows排序規則的非Unicode資料，在排序的效能上與Unicode資料幾乎沒有差異。
SQL Server使用非Unicode排序規則的唯一場合是定義了SQL排序規則的非Unicode資料。這種情況下，排序和掃描通常比應用Unicode排序規則更快速。Unicode排序規則應用於所有Unicode資料，無論定義使用Windows排序規則還是SQL排序規則。
其次，大量資料的排序，Unicode可能比非Unicode要慢，因為資料以雙位元組儲存。另一方面，對亞洲語言字元而言，排序Unicode儲存的資料比排序DBCS方式儲存的資料要快速，因為DBCS資料實際上是單位元組和雙位元組寬度的混合，而Unicode字元則是固定寬度的（2個位元組）。
其他的效能問題主要決定於SQL Server例項與客戶端之間的編碼機制轉換問題。一般地，client/server內碼表轉換對效能的影響可以說是微不足道的。
大多數時候，決定將資料即使是non-DBCS資料儲存為Unicode，應當更多的是基於業務需要而非效能考慮。在Internet快速發展的全球化經濟時代，支援執行不同語言與區域的客戶端計算機變得比以往任何時候更加重要。此外，選擇一種能支援全世界範圍內使用者需要的所有字元的內碼表，變得日益困難。

參考