SQL Server 索引導致查詢條件執行順序改變引發轉型錯誤案例分析

現象：同一張表，WHERE item_local_code='03004' AND DATEDIFF(day, sub_para, GETDATE())=29 偶發「轉換 datetime 失敗」，但 sub_para 為 varchar(8) 儲存日期字串，部分列含髒資料 (如 '2003a1-1')。錯誤發生與否取決於**執行計畫中條件評估順序**，而順序受索引影響。

重現腳本與錯誤示範

CREATE TABLE tb (
    item_local_code CHAR(5),
    sub_del_flag INT,
    sub_para VARCHAR(10),
    CONSTRAINT PK_t PRIMARY KEY (sub_para, item_local_code) -- 叢集索引鍵順序
);
INSERT tb SELECT '03004', 1, '2003-1-1'
UNION ALL SELECT '03005', 1, '2003a1-1'; -- 髒資料

-- 查詢：優化器可能先用 PK 索引求 sub_para，導致髒資料參與 DATEDIFF 轉型報錯
SELECT * FROM tb
WHERE item_local_code='03004'
  AND sub_del_flag<>0
  AND DATEDIFF(day, sub_para, GETDATE()) > 29;
-- Msg 241: 轉換字串為 datetime 時發生語法錯誤。

根因分析：索引決定存取路徑與評估順序

• 叢集索引 (sub_para, item_local_code) 使優化器傾向 **Index Seek → Key Lookup** 先篩選 sub_para。
• 執行計畫中 DATEDIFF(day, sub_para, ...) 運算式在 **Filter 運算子** 中評估，**早於** item_local_code 篩選。
• 髒資料 '2003a1-1' 被讀取並嘗試轉型 → 拋錯，即使最終不符合 item_local_code='03004' 也會報錯。

解決方案：強制安全評估順序

方法一：子查詢/CTE 先篩選安全列再轉型 (推薦)

SELECT * FROM (
    SELECT * FROM tb
    WHERE item_local_code='03004'
      AND sub_del_flag<>0
      AND ISDATE(sub_para)=1 -- 先過濾合法日期格式
) AS CleanData
WHERE DATEDIFF(day, sub_para, GETDATE()) > 29;

內層查詢先套用 ISDATE=1 過濾髒資料，外層才執行日期運算，邏輯順序固定不受索引影響。

方法二：使用 TRY_CONVERT / TRY_CAST (SQL Server 2012+)

SELECT * FROM tb
WHERE item_local_code='03004'
  AND sub_del_flag<>0
  AND TRY_CONVERT(DATE, sub_para) IS NOT NULL
  AND DATEDIFF(day, TRY_CONVERT(DATE, sub_para), GETDATE()) > 29;

TRY_CONVERT 失敗回傳 NULL 不拋錯，配合 IS NOT NULL 完成安全過濾。

方法三：建立覆蓋索引引導優化器

CREATE INDEX IX_tb_SafeQuery ON tb (item_local_code, sub_del_flag) INCLUDE (sub_para);

優化器改用此非叢集索引直接求 item_local_code，大幅降低讀取髒資料機率 (但非 100% 保證，仍建議配合方法一)。

最佳實務總結

1. 資料品質優先：來源端攔截髒資料，欄位型別改用 DATE/DATETIME 而非字串。
2. 防禦性寫法：任何字串轉型操作前，務必加 ISDATE=1 或 TRY_CONVERT 守衛。
3. 勿依賴評估順序：SQL 標準不保證 WHERE 條件評估順序，優化器可自由重排。
4. 索引設計考量查詢模式：高選擇性謂詞優先放入索引鍵前段，減少不必要資料讀取。