Mysterious conditional where clause index choices

Have you got an explanation, why query optimizer is choosing different indexes and modes in this example?

/* 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;

What you're seeing is constant folding.

More info at this link:


Constant folding is a technique the optimizer uses to remove any unnecessary code to help improve performance. Constant Folding does this by removing unnecessary variables and simplifying the query before compiling the plan.

OPTION (RECOMPILE) allows constant folding of @Step and @FindNo in your second query. Since @Step is known to equal 1, the line OR (@Step = 2 AND CustNo2 = @FindNo) will be optimized away. Notice that this affects estimated rows.

If you want to see a more drastic example of this, try the below code. Notice how constant folding (enabled by option recompile) allows the second query plan to completely avoid the union, since the optimizer knows no rows could be returned.

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)

StackExchange Database Administrators Q#193238, answer score: 8