TSQL Why is Top faster with a variable? Morning all,

I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications.

I added in a select to obtain the top record from a table within a subquery (if thats makes sense)

the query took nearly 3minutes to return a final result set of 100 records using 

SELECT TOP 1 ...


I had a look online for improvements to what I was trying to achieve and it was suggested that I change my select to use a variable, as below

DECLARE @topCount INT
SET @topCount = 1

SELECT TOP (@topCount) ...


This took the same query from the 3minutes down to 1second, which is great!

But can anyone explain why this is so.
When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible.

When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible.

In your case the query plan generated with a row goal of 100 is the better plan to use even when you only want one row.

To verify you can try to add option (recompile) to your query with the variable. In that case SQL Server will use the current value of @topCount as a row goal and since that is 1 you should get the slow plan.