排序規則定義字元的位元模式、比較規則與排序順序。SQL Server 2005+ 名稱結構:字元集_大小寫_重音_假名_全半角_二進位,例如 `Chinese_PRC_CI_AS_WS`。
| 後綴 | 含義 | 值 |
|---|---|---|
| CI / CS | Case Insensitive / Sensitive | 不區分/區分大小寫 |
| AI / AS | Accent Insensitive / Sensitive | 不區分/區分重音 |
| KI / KS | Kana Insensitive / Sensitive | 不區分/區分平假名/片假名 |
| WI / WS | Width Insensitive / Sensitive | 不區分/區分全形/半形 |
| BIN / BIN2 | Binary Sort | 依碼位值比較,效能最佳但非語意排序 |
跨資料庫、跨伺服器、或同資料庫不同欄位定義不同 Collation 時,進行 `=`, `JOIN`, `UNION`, `LIKE`, `IN` 等字串比較操作,將拋出錯誤:
無法解決 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之間的排序規則衝突。
CREATE TABLE TableA (TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN);
CREATE TABLE TableB (TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS);
SELECT * FROM TableA A JOIN TableB B ON A.TagName = B.TagName; -- 報錯
-- 指定一方轉換為另一方
SELECT * FROM TableA A JOIN TableB B ON A.TagName = B.TagName COLLATE Chinese_PRC_CI_AS;
-- 或使用 DATABASE_DEFAULT 讓引擎自動取資料庫預設排序
SELECT * FROM TableA A JOIN TableB B ON A.TagName COLLATE DATABASE_DEFAULT = B.TagName COLLATE DATABASE_DEFAULT;
優點: 即時生效、無結構變更風險。
缺點: 導致索引失效 (Index Scan)、查詢計畫無法重用、需修改所有受影響查詢。
ALTER TABLE TableA ALTER COLUMN TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS NOT NULL;
注意:
-- 1. 查詢伺服器/資料庫/欄位目前排序規則
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
SELECT DATABASEPROPERTYEX('DBName', 'Collation') AS DBCollation;
SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');
-- 2. 尋找所有非預設排序規則的欄位
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
o.name AS TableName,
c.name AS ColumnName,
c.collation_name AS ColumnCollation
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')
AND o.type = 'U';