SQL Server 排序規則衝突完整解析：Collation 名稱結構、衝突原因與三大解決方案

跨資料庫、跨伺服器查詢時，若聯結欄位排序規則不同，會拋出「無法解決排序規則衝突」錯誤。本文系統解析 Collation 名稱結構、衝突觸發條件，提供三種解決方案（查詢層指定、資料庫層統一、欄位層修改），並附最佳實務建議。

什麼是排序規則

Collation 定義：

• 字元位元組模式（編碼）
• 排序與比較規則：大小寫、重音、假名、全半角敏感度

Collation 名稱結構 (SQL 2005+)

Chinese_PRC_CI_AI_WS
│          │  │  │
│          │  │  └─ WS: 寬度敏感 (Width Sensitive) / WI: 不敏感
│          │  └──── AI: 重音不敏感 (Accent Insensitive) / AS: 敏感
│          └────── CI: 大小寫不敏感 (Case Insensitive) / CS: 敏感
└───────────────── 語言/字元集：Chinese_PRC (大陸簡體)

常見後綴對照表

衝突觸發範例

CREATE TABLE TagsTableA (TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN);
CREATE TABLE TagsTableB (TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS);

SELECT * FROM TagsTableA A INNER JOIN TagsTableB B ON A.TagName = B.TagName;
-- 錯誤：無法解決 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之間的排序規則衝突

三大解決方案

方案 1：查詢層指定 COLLATE（臨時、無結構變更）

SELECT * FROM TagsTableA A 
INNER JOIN TagsTableB B 
  ON A.TagName COLLATE Chinese_PRC_CI_AS = B.TagName;  -- 強制統一

• 優點：立即生效、不需權限、不影響現有資料。
• 缺點：每次查詢需撰寫、可能導致索引失效 (Scan)、效能較差。

方案 2：資料庫層統一預設 Collation（根治新物件）

-- 僅影響後續建立的資料表/欄位，現有物件不變
ALTER DATABASE MyDB COLLATE Chinese_PRC_CI_AS;

• 優點：新物件自動繼承、長期一致。
• 缺點：現有衝突欄位仍需個別處理、需獨佔資料庫鎖定。

方案 3：欄位層修改 Collation（徹底解決既有衝突）

ALTER TABLE TagsTableA ALTER COLUMN TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS;

• 優點：徹底消除衝突、索引可正常 Seek。
• 缺點：大表需重建、鎖定時間長、需規劃維護窗口、外鍵/索引/檢查約束可能需先移除再建立。

最佳實務建議

1. 專案啟動時統一規範：全組資料庫/資料表/欄位採用同一 Collation (建議 Chinese_PRC_CI_AS 或 Latin1_General_CI_AS)。
2. 避免跨庫聯結不一致欄位：ETL/同步階段統一轉換。
3. 大表修改 Collation 策略：
   • 建立新欄位 (正確 Collation) → 分批更新資料 → 切換欄位名 → 刪除舊欄位。
   • 或使用 SELECT ... INTO 新表 重建法。
4. 測試環境驗證：修改前後執行代表性查詢，比對執行計畫與結果筆數。

建立統一 Collation 規範並納入程式碼審查，能從源頭杜絕排序規則衝突，避免上線後緊急修補帶來的風險與停機。