Hmmm... I played with this a little and the number of rows certainly makes a difference.

I added 10,000 rows (actually 10,001 - as below) and this time query 1 gave the only table scan and query 4 went for nested loops joining an Index Seek and a record id lookup, which is what I had expected it to do when I saw the question.

For the question as asked, I accept I got it wrong (I said Q1 as I thought all the others would perform an index seek but Q1 could not), but it is interesting how the question is not a simple one of the structure leading to a deterministic result, but the optimiser may take very different routes in the same database structures depending on other factors such as data volumes.

WITH cte (Num) AS

(

SELECT 0 Num

UNION ALL

SELECT Num + 1

FROM cte

WHERE Num < 10000

)

INSERT

INTO t (id, ch, na, flag)

SELECT num ,

CHAR(Num%128 + 50) ,

CAST(num AS VARCHAR),

CASE

WHEN Num%2 = 1

THEN 'Y'

ELSE 'N'

END

FROM cte OPTION(maxrecursion 10000)