This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and in the second it takes 35 SECONDS.
Here's the details.
I created test table named TEST with only two columns - LastName and FirstName. It's a heap with no indexes of any kind. It has exactly 1 Million rows and no nulls.
(I run DBCC DROPCLEANBUFFERS before each query)
When I execute "SELECT FirstName FROM Test WHERE LastName = 'Smith'" the execution plan shows a Table Scan and Statistics IO shows the total logical reads as 25,923. The query takes less than 1 second to execute.
Then, I execute "SELECT * FROM Test" and again the execution plan shows a Table Scan and Statistics IO shows logical reads of 25,923. BUT, this query takes 35 seconds to complete.
I've tried reversing the order of the queries, restarting the whole server in between queries, etc. But, the results are always the same.
Unless those execution plans or statistics are lying in one of the cases, it makes no sense that there should be a 35x times difference.
How can I figure out what accounts for this difference?