撰寫跨語言可攜式 Transact-SQL 程式碼：日期、Unicode、排序規則三大關鍵

前言

SQL Server 7.0 引入 Unicode、排序規則等國際化功能，SQL Server 2005 更支援欄位級排序規則。本文針對開發跨語言可攜式 T-SQL 程式碼的三大核心挑戰：日期處理、Unicode 字串、排序規則相依性，提供具體解決方案。

一、日期處理：避免文化特有假設

1. 週起始日差異

SET LANGUAGE us_english; SELECT DATEPART(dw, '20060606'); -- 返回 3 (週三)
SET LANGUAGE British;  SELECT DATEPART(dw, '20060606'); -- 返回 2 (週二)
-- @@DATEFIRST 分別為 7 (美) 與 1 (英)

解決方案

• 方案 A：程序開頭 SET DATEFIRST 7 統一假設 (會覆蓋連線設定)
• 方案 B：正規化 @@DATEFIRST，使結果與語言無關

  DECLARE @ndf_dw INT;
  SET @ndf_dw = (@@DATEFIRST + DATEPART(dw, '20060606')) % 7;
  -- @ndf_dw 在美/英環境下均返回相同值
  

2. 日期名稱本地化

• DATENAME(dw, ...) 返回當地語言名稱 (如 Tuesday / Mardi)
• 比較/儲存請用數值日期部分 (DATEPART、MONTH、DAY)
• 顯示給使用者才用名稱，建議由用戶端格式化，或確保登入語言正確

3. 日期常數的可攜寫法

二、Unicode：最簡單的可攜方案

• 字串欄位/變數優先用 nchar / nvarchar / ntext 取代 char/varchar/text
• Unicode 字串常數必須加 N 前綴：N'Ω' 而非 'Ω'
• 缺少 N 前綴會導致隱性轉換為資料庫預設代碼頁，造成字元遺失 (如希臘字母 Ω 變成 O)
• RPC 參數傳遞可免除 N 前綴需求 (參數本身攜帶型別資訊)

實證範例

CREATE TABLE #t1 (c1 NCHAR(1));
INSERT #t1 VALUES (N'Ω');  -- 正確儲存 Ω
INSERT #t1 VALUES ('Ω');   -- 轉換為代碼頁，若無 Ω 則存成最接近字元 (如 O)
SELECT * FROM #t1;         -- 返回 Ω 和 O

三、排序規則相依性：隱性 Bug 來源

字元比較 (@c < '0') 結果取決於排序規則定義的字碼順序與比較規則。預設 SQL_Latin1_General_CP1_CI_AS 下 '0'~'9'、'A'~'Z' 連續，但其他排序規則不保證。

錯誤範例：十六進位轉換函數

IF @c < '0' OR @c > 'F' RETURN NULL;  -- 依賴字元編碼順序，非所有排序規則安全

修正方案

• 明確列出合法字元集合：IF @c NOT IN ('0','1',...,'9','A','B','C','D','E','F')
• 或使用 ISNUMERIC / PATINDEX 等不依賴排序規則的函數
• 欄位/資料庫/執行個體層級指定排序規則，確保行為一致

總結檢查清單

1. 日期常數用 yyyymmdd 或 ODBC 轉義 / CONVERT 樣式
2. 比較/分組用數值日期部分，避免 DATENAME
3. 字串欄位/變數預設用 Unicode 型別
4. Unicode 常數加 N 前綴
5. 避免字元範圍比較 (@c BETWEEN 'A' AND 'Z')，改用 IN 或模式比對
6. 測試環境涵蓋目標語言/排序規則組合