performance - SQL Server Query: Fast with Literal but Slow with Variable

Question

I have a view that returns 2 ints from a table using a CTE. If I query the view like this it runs in less than a second

SELECT * FROM view1 WHERE ID = 1

However if I query the view like this it takes 4 seconds.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id

I've checked the 2 query plans and the first query is performing a Clustered index seek on the main table returning 1 record then applying the rest of the view query to that result set, where as the second query is performing an index scan which is returning about 3000 records records rather than just the one I'm interested in and then later filtering the result set.

Is there anything obvious that I'm missing to try to get the second query to use the Index Seek rather than an index scan. I'm using SQL 2008 but anything I do needs to also run on SQL 2005. At first I thought it was some sort of parameter sniffing problem but I get the same results even if I clear the cache.

Answer 1

Probably it is because in the parameter case, the optimizer cannot know that the value is not null, so it needs to create a plan that returns correct results even when it is. If you have SQL Server 2008 SP1 you can try adding OPTION(RECOMPILE) to the query.