SQL Server WHERE 變數 IS NULL 導致全表掃描？實測驗證與動態 SQL 解決方案

問題現象

動態條件查詢常寫成：

WHERE (@ProjectIds IS NULL OR ProjectId = @ProjectIds)
  AND (@Scores IS NULL OR Score = @Scores)

直覺以為優化器能「短路」，實測完全失效，索引不被使用。

實測環境

CREATE TABLE aaa (id INT IDENTITY, name VARCHAR(12), age INT);
CREATE INDEX idx_age ON aaa(age);
INSERT INTO aaa(name, age) 
SELECT CAST(number AS VARCHAR), number FROM master..spt_values WHERE type='P' AND number < 10000;

測試語句與執行計劃

DECLARE @i INT = 100;

-- 1. 變數 IS NULL 在前
SELECT * FROM aaa WHERE (@i IS NULL OR age = @i);  -- ❌ Clustered Index Scan

-- 2. 變數 IS NULL 在後
SELECT * FROM aaa WHERE (age = @i OR @i IS NULL);  -- ❌ Clustered Index Scan

-- 3. ISNULL 包裝
SELECT * FROM aaa WHERE age = ISNULL(@i, age);     -- ❌ Clustered Index Scan

-- 4. 直接比較 (對照組)
SELECT * FROM aaa WHERE age = @i;                  -- ✅ Index Seek

結論：SQL Server (同 Oracle) 遇到 變數 IS NULL 邏輯，優化器無法參數化嗅探，退化為全表掃描。

解決方案：動態 SQL 構建

DECLARE @i INT = 100;
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM aaa';
IF @i IS NOT NULL
    SET @sql += N' WHERE age = @i';
EXEC sp_executesql @sql, N'@i INT', @i;

優點：只有真正有值時才加 WHERE，計畫穩定走索引。

多條件擴展版

DECLARE @ProjectIds INT = 5, @Scores INT = NULL;
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Comment WITH (NOLOCK) WHERE 1=1';
DECLARE @params NVARCHAR(MAX) = N'';

IF @ProjectIds IS NOT NULL BEGIN
    SET @sql += N' AND ProjectId = @ProjectIds';
    SET @params += N'@ProjectIds INT,';
END
IF @Scores IS NOT NULL BEGIN
    SET @sql += N' AND Score = @Scores';
    SET @params += N'@Scores INT,';
END

-- 移除尾隨逗號
SET @params = LEFT(@params, LEN(@params) - 1);
EXEC sp_executesql @sql, @params, @ProjectIds, @Scores;

替代方案對比