SQL Server COLLATE 子句陷阱：排序規則衝突錯誤排查實錄

執行腳本在大多數資料庫正常，唯獨某資料庫報錯 Msg 446: Cannot resolve collation conflict for equal to operation。查閱線上說明發現：COLLATE 可套用於資料庫、資料欄、運算式三個層級。錯誤原因是比較運算兩邊欄位/運算式的排序規則不相容（如一邊大小寫敏感、另一邊不敏感）。解決方法：在比較運算式明確指定 COLLATE database_default 或統一資料庫/欄位排序規則。

COLLATE 三大應用層級

1. 資料庫層級：CREATE DATABASE ... COLLATE Chinese_PRC_CI_AS 或 ALTER DATABASE。
2. 欄位層級：CREATE TABLE ... col VARCHAR(10) COLLATE Latin1_General_CS_AS。暫存表可用 COLLATE database_default 繼承使用者資料庫排序規則而非 tempdb。
3. 運算式層級：WHERE col1 = col2 COLLATE database_default，強制轉換排序規則以避免衝突。

識別子排序規則

• 執行個體層級物件（登入、資料庫名稱）使用執行個體預設排序規則。
• 資料庫內物件（資料表、檢視表、欄位名）使用資料庫預設排序規則。
• 變數、標籤、暫存程序/表使用執行個體預設排序規則（跨資料庫批次需注意）。

實戰修正範例

-- 錯誤：比較兩不同排序規則欄位
SELECT * FROM db1.dbo.t1 JOIN db2.dbo.t2 ON t1.name = t2.name;

-- 修正：指定其中一方轉換
SELECT * FROM db1.dbo.t1 JOIN db2.dbo.t2 ON t1.name = t2.name COLLATE database_default;