Mysterious conditional where clause index choices
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)