神秘的條件 where 子句索引選擇

你有沒有解釋一下，為什麼查詢優化器在這個例子中選擇了不同的索引和模式？

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
   ID INT IDENTITY PRIMARY KEY
   ,CustNo INT NULL
   ,CustNo2 INT NULL
   );

-- populate with data
WITH
 L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
 L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
   OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
   OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
   SELECT CustNo
   FROM #Test
   WHERE CustNo = @FindNo;

你看到的是不斷折疊。

此連結的更多資訊：

常量折疊是優化器用來刪除任何不必要的程式碼以幫助提高性能的一種技術。常量折疊通過在編譯計劃之前刪除不必要的變數和簡化查詢來做到這一點。

OPTION (RECOMPILE) 允許在第二個查詢中不斷折疊 @Step 和 @FindNo。由於已知@Step 等於 1，因此該行將OR (@Step = 2 AND CustNo2 = @FindNo)被優化掉。請注意，這會影響估計的行數。

如果你想看一個更激烈的例子，試試下面的程式碼。請注意常量折疊（通過選項重新編譯啟用）如何允許第二個查詢計劃完全避免聯合，因為優化器知道不會返回任何行。

CREATE TABLE #t (
id int)

INSERT #t
VALUES (1)

DECLARE @v char(5) = 'false'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'
OPTION (RECOMPILE)

引用自：https://dba.stackexchange.com/questions/193238