I would have thought the two following batches were equivalent. SELECT last_modified_datetime ,list_number FROM dbo.mytable WHERE last_modified_datetime >= (select dateadd(mi,-30,max(last_modified_datetime)) from dbo.another_table) and another_col = '10' and list_number is not null GO DECLARE @last_modified_datetime as datetime; SET @last_modified_datetime = (select dateadd(mi,-30,max(last_modified_datetime)) from dbo.another_table); SELECT last_modified_datetime ,list_number FROM dbo.mytable WHERE last_modified_datetime >= @last_modified_datetime and another_col = '10' and list_number is not null GO My execution plan begs to differ. The first does a seek on `last_modified_datetime` in the expected index. There will only ever be about 100 rows modified in the last 30 minutes. So it's nice and quick. In the second, it's as if the execution plan is saying "Geez buddy, I have no idea what `@last_modified_datetime` is. How about we try something else? There's a good lad." Why would this be?
Without seeing the execution plans, I'm speculating, but... the local variable results in a a sample against the statistics of the column while the sub-query is probably converted to a join and either the stats are not sampled or a different mechanism is employed. Those are the behaviors I'd expect, but again, I'm going off what I can see, not all the evidence. Just as an experiment, try putting the query with the variable into a procedure and substitute the variable for a parameter and see what parameter sniffing can do for you.
It is not recommended to use the local variables. This is where the problem occurs. When you use local variable, the optiizer somehow is trying to work on another_col = '10' and list_number IS NOT NULL before the datetime predicate. But in the sub-query mode, the datetime filter is applied first which results in an index seek. So as per recommendations, if this is a stored procedure, populate the variables before passing it to the procedure. This will hopefully give an optimal execution plan.
In this case I agree with @Sacred Jewel and @rafat. The local variable makes the optimizer to guess the selectivity, on the other hand parameters may cause parameter sniffing. You could take a look at this presentation:
http://www.microsoftpdc.com/2009/SVR31 If you suffer from parameter sniffing, you could try to use the "optimize for unknown" query hint.