SQL Server 預設安裝排序規則通常為 **不區分大小寫 (Case-Insensitive, CI)**,如 `Chinese_PRC_CI_AS`。密碼驗證、代碼比對、區分大小寫代碼表等場景需**區分大小寫 (Case-Sensitive, CS)**。本文詳解兩大標準方案:`VARBINARY` 轉換法與 `COLLATE` 指定排序規則法,並完整解析 Collation 後綴含義。
原理:字元編碼 (ASCII/Unicode) 下,大小寫字元碼值不同 (如 `'a'=0x61`, `'A'=0x41`)。轉為 `VARBINARY` 後直接比較二進位位元組序列,天然區分大小寫、重音、寬度等所有差異。
-- 範例:驗證密碼 'Admin' (區分大小寫)
SELECT *
FROM T_User
WHERE CAST(Password AS VARBINARY) = CAST('Admin' AS VARBINARY);
在比較運算式中顯式指定 **CS (Case-Sensitive)** 排序規則,優化器**可使用索引** (若欄位預設排序規則相容或建立相同排序規則索引)。
-- 範例:用戶名不區分大小寫,密碼區分大小寫
SELECT *
FROM T_User
WHERE UserName = 'admin' -- 使用資料庫/欄位預設 CI 排序規則
AND Password COLLATE Chinese_PRC_CS_AS_WS = 'Admin'; -- 指定 CS 排序規則
格式:`<語言/字集>_
| 後綴 | 全稱 | 含義 | 範例差異 |
|---|---|---|---|
| CI / CS | Case Insensitive / Sensitive | **是否區分大小寫** | CI: 'a'='A' ; CS: 'a'≠'A' |
| AI / AS | Accent Insensitive / Sensitive | **是否區分重音/變音符號** | AI: 'é'='e' ; AS: 'é'≠'e' (法文/德文關鍵) |
| KI / KS | Kana Insensitive / Sensitive | **是否區分日語平假名/片假名** | KI: 'ひらがな'='ヒラガナ' ; KS: 不等 |
| WI / WS | Width Insensitive / Sensitive | **是否區分全形/半形 (寬度)** | WI: 'A' (全形) = 'A' (半形) ; WS: 不等 |
| BIN / BIN2 | Binary / Binary Code Point | **二進位比較** (最快、最嚴格,忽略所有語言規則) | BIN: 舊版排序邏輯;BIN2: 真正 Code Point 比較 (推薦) |
| VSS / _100 / _140 | Variation Selector Sensitive / 版本 | 支援 Unicode 變體選擇器 / 排序規則版本 (SQL 2008/2012/2017+) | _140 為 SQL 2017+ 最新版,支援補充字元 |
CREATE TABLE T_User (
UserName NVARCHAR(50) COLLATE Chinese_PRC_CI_AS, -- 不區分大小寫
Password NVARCHAR(100) COLLATE Chinese_PRC_CS_AS -- 區分大小寫
);
查詢時無需 `COLLATE` 子句,欄位自帶排序規則。
CREATE DATABASE MyDB COLLATE Chinese_PRC_CS_AS; -- 整庫預設區分大小寫
-- 或修改現有庫 (需單用戶模式)
ALTER DATABASE MyDB COLLATE Chinese_PRC_CS_AS;
影響 `master`、`tempdb`、變數、游標、GOTO 標籤等系統物件比較行為。**安裝時務必審慎選擇**,生產環境強烈建議預設 `CI_AS`,查詢層級按需 `COLLATE CS`。
| 場景 | 推薦方案 | 理由 |
|---|---|---|
| 臨時腳本、小表、動態 SQL | `CAST(... AS VARBINARY)` | 寫法快、無索引需求 |
| 生產查詢、大表、需走索引 | `COLLATE Chinese_PRC_CS_AS` (或欄位預設 CS) | **SARGable,可用索引 Seek**,效能穩定 |
| 整張表/資料庫預設區分大小寫 | 欄位/資料庫定義 `COLLATE ..._CS_...` | 一次設定、處處生效,避免漏加 COLLATE |
| 最高效能、純二進位比對 (雜湊、Token、Base64) | `COLLATE Chinese_PRC_BIN2` 或 `VARBINARY` | 無語言規則開銷,CPU 最省 |
**區分大小寫本質是排序規則屬性**。生產環境**首選 `COLLATE Chinese_PRC_CS_AS` (或 `_BIN2`)**,保留索引可用性;`VARBINARY` 僅作臨時/小表備用。建表時對敏感欄位 (密碼、Token、代碼) 直接指定 `CS`/`BIN2` 排序規則,是架構層面的最佳防禦。切記:**無 `ORDER BY` 不保證順序;無 `COLLATE` 不保證大小寫語義**。