SQL Server 與 Analysis Services 排序規則差異影響與選擇策略

前言

SQL Server 資料庫引擎與 Analysis Services (SSAS) 可獨立設定排序規則，但兩者不一致會導致查詢結果排序、比較、分組行為差異，進而造成報表不一、ETL 失敗、使用者困惑。本文深度分析兩者支援的排序規則體系、不一致風險、選擇決策矩陣 (二進位、Windows、SQL Server 三大類)、實務建議與驗證腳本。

一、兩大引擎支援的排序規則體系

二、排序規則不一致的具體風險

1. 排序順序不同：相同資料在引擎與 SSAS 中 ORDER BY 結果順序不一，導致報表呈現差異。
2. 比較/分組語意不同：WHERE Col = 'value'、GROUP BY Col、DISTINCT、JOIN 條件在大小寫、重音、全半形、寬窄字元敏感度上判斷不同。
3. ETL/資料同步失敗：從引擎抽取資料載入 SSAS 時，鍵值比對失敗 (如 'Taipei' vs 'taipei' 在 CI 視為相同，但在 CS 或 Binary 視為不同)。
4. 跨伺服器查詢 (Linked Server/OPENQUERY) 錯誤：排序規則衝突導致「無法解析排序規則衝突」錯誤。

三、三大排序規則類型選擇決策矩陣

1. 二進位排序規則 (Binary / Binary2) — 效能最優、語意最簡單

• 原理：直接比較字元編碼位元模式 (Code Point)，不考慮語言規則。
• 優點：CPU 開銷最低、索引掃描最快、不受語言/地區設定影響、行為確定性最高。
• 缺點：'a' < 'A' < 'B' (依 ASCII/Unicode 編碼順序)，不符合人類直覺排序；NCHAR/NVARCHAR (Unicode) 與 CHAR/VARCHAR (非 Unicode) 比較規則不同，混用時結果易混淆。
• 適用場景：
  • 資料純為機器產生/處理 (雜湊、Base64、GUID、二進位序列化)。
  • 效能極度敏感、查詢模式為精確比對 (=、IN、JOIN) 而非範圍排序。
  • 資料庫僅含單一字元資料類型 (全 Unicode 或全非 Unicode)，避免混用陷阱。
• 建議：優先評估，若業務允許 (不需人類友善排序、無混用類型)，直接採用 Latin1_General_100_BIN2 或 Chinese_Taiwan_Stroke_90_BIN2。

2. Windows 排序規則 — 語意正確、跨引擎一致

• 原理：依 Unicode Collation Algorithm (UCA) + Windows 地區設定定義比較/排序權重。
• 優點：符合人類語言習慣 (大小寫、重音、假名、寬窄字元敏感度可控)；**SSAS 唯一支援**，保證引擎與 SSAS 行為一致；支援 Unicode 完整字元集。
• 適用場景 (滿足任一即建議採用)：
  1. 同時部署 SSAS，且要求兩者結果一致 (最強理由)。
  2. 資料庫混用 NCHAR/NVARCHAR (Unicode) 與 CHAR/VARCHAR (非 Unicode)，且應用程式需跨類型比較/聯結。
  3. 應用程式主要使用 CHAR/VARCHAR，且極少使用 LIKE 模糊查詢 (Windows Collation 對 LIKE 優化較弱，見下)。
• 版本建議：SQL Server 2008+ 請優先選擇 _100 系列 (如 Chinese_Taiwan_Stroke_CI_AS → Chinese_Taiwan_Stroke_100_CI_AS)，包含增補字元支援、權重表更新。

3. SQL Server 排序規則 — 相容舊版、LIKE 效能較佳

• 原理：SQL Server 7.0/2000 時代專有排序邏輯，非 Unicode 資料使用代碼頁對應表，Unicode 資料則類似 Windows Collation 但細節不同。
• 優點：LIKE、PATINDEX、CHARINDEX 等模式比對在大量資料時效能優於 Windows Collation (特別是前綴搜尋 LIKE 'abc%')。
• 缺點：與 Windows/SSAS/Excel/程式語言排序不一致；Unicode 支援有限；新版功能 (增補字元、_100/_140 權重) 不支援。
• 適用場景：
  • 舊版應用遷移，無法修改程式碼驗證排序行為變更。
  • 核心查詢大量使用 LIKE 'prefix%' 且已建立索引，經實測 Windows Collation 明顯較慢 (罕見，需實測驗證)。
  • 團隊接受「資料庫排序 ≠ Excel/程式語言排序」的差異。

四、決策流程圖 (文字版)

開始
│
├─ 是否部署 SSAS 且要求結果一致？ ──是──→ 強制選 Windows Collation (_100 系列) → 結束
│
├─ 資料是否純機器處理、無人類排序需求、單一字元類型？ ──是──→ 選 Binary2 (BIN2) → 結束
│
├─ 是否大量使用 LIKE 'prefix%' 且經實測 Windows Collation 顯著較慢？ ──是──→ 評估 SQL Server Collation (風險自負) → 結束
│
└─ 預設選擇 → Windows Collation (_100 系列，依地區選 Chinese_Taiwan_Stroke_100_CI_AS 等) → 結束

五、驗證與變更腳本

1. 查詢目前資料庫/資料行排序規則

-- 資料庫預設
SELECT name, collation_name FROM sys.databases WHERE name = 'YourDB';

-- 資料行層級
SELECT name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('YourTable') AND collation_name IS NOT NULL;

2. 變更資料庫預設排序規則 (需獨佔存取)

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB COLLATE Chinese_Taiwan_Stroke_100_CI_AS;
ALTER DATABASE YourDB SET MULTI_USER;

3. 變更資料行排序規則 (需重建索引/約束)

ALTER TABLE YourTable ALTER COLUMN ColName NVARCHAR(100) COLLATE Chinese_Taiwan_Stroke_100_CI_AS;

六、總結

1. SSAS 共存 → 必須 Windows Collation，這是鐵律，除非業務明確接受不一致。
2. 效能優先、機器資料、單一類型 → Binary2 (BIN2)，最快、最簡單。
3. 一般業務應用 → Windows Collation _100 系列，語意正確、跨平台一致、支援完整 Unicode。
4. SQL Server Collation 僅作舊版相容/極特定 LIKE 效能優化考量，新專案不建議採用。
5. 安裝前規劃、安裝後驗證、變更謹慎執行** (需停機、重建索引、測試應用相容性)。