SQL Server 字串大小寫敏感比較：二進位轉換與排序規則兩大實戰方案

SQL Server 預設大小寫不敏感 (CI)，導致 'admin' = 'ADMIN' 為真。密碼驗證、代碼比對、大小寫敏感業務鍵等場景需啟用大小寫敏感 (CS)。本文詳解 二進位轉換法與 排序規則指定法的語法、效能差異、適用範圍與最佳實務。

方案一：CAST 轉 VARBINARY 比較

SELECT * 
FROM T_User 
WHERE CAST(UserName AS VARBINARY) = CAST('Admin' AS VARBINARY);

• 原理： 字元轉為二進位位元組序列，直接比較位元組值，'A'(0x41) ≠ 'a'(0x61)
• 優點： 無需修改資料庫/欄位/表屬性，臨時查詢、舊系統相容性佳
• 缺點： 導致索引失效 (SARGable 破壞)，全表掃描；每列轉換 CPU 開銷
• 適用： 單次腳本、資料清洗、無索引小表、無法修改排序規則環境

方案二：COLLATE 指定排序規則

-- 1. 字串常數指定
WHERE Password COLLATE Latin1_General_CS_AS = 'Admin'

-- 2. 欄位指定 (若欄位預設 CI)
WHERE UserName = 'admin' AND Password COLLATE Chinese_PRC_CS_AS_WS = 'Admin'

-- 3. 資料庫/欄位永久變更 (需獨佔鎖)
ALTER TABLE T_User ALTER COLUMN Password NVARCHAR(50) COLLATE Latin1_General_CS_AS;
ALTER DATABASE MyDB COLLATE Latin1_General_CS_AS;

排序規則命名解碼：Chinese_PRC_CS_AS_WS

效能對比實測 (百萬列表、索引於 Password)

關鍵發現： COLLATE 若與索引鍵排序規則相容 (同 CS/AS/WS)，可保持 Index Seek；CAST VARBINARY 必然導致掃描。

最佳實務建議

1. 新系統設計： 資料庫/欄位層級預設 CS_AS_WS (或 Latin1_General_100_CS_AS_SC)，從根源解決。
2. 既有系統改造： 欄位層級 ALTER COLUMN ... COLLATE ..._CS_AS，僅影響該欄位，風險可控。
3. 臨時查詢/腳本： 使用 COLLATE Latin1_General_CS_AS，保持 SARGable。
4. 避免： CAST(... AS VARBINARY) 用於 WHERE 子句大表查詢。
5. 密碼儲存： 永遠儲存雜湊值 (BCrypt/Argon2/PBKDF2)，而非明文或可逆加密。比較時比對雜湊，天然大小寫敏感，無需排序規則技巧。

跨資料庫/伺服器比較陷阱

-- 連結伺服器查詢，排序規則衝突
SELECT * FROM LocalDB.dbo.T1 
JOIN LinkedSvr.RemoteDB.dbo.T2 ON T1.Code COLLATE Latin1_General_CS_AS = T2.Code;
-- 必須顯式指定一方排序規則，否則報錯 "Cannot resolve collation conflict"

掌握排序規則機制，能在不犧牲效能前提下，精準控制字串比較語意，是多語系、高安全系統的必修課。