I’m surprised that only very few people are interested in the actual SQL issue itself. It seems to me that there must be something wrong with the way Jeff measured the queries. My experience is that SQL Server (as any other modern database) is much smarter in most cases that we are, and that if you come up (according to you) with an optimal query plan, SQL Server usually does not perform worse, and usually it does something better. There are definitely edge cases, but simple queries like the ones Jeff is talking about, should be straightforward.
First, I hope that Jeff is intentionally omitting the ORDER BY clause from the simplified snippets:
select top 48 Id from Posts
select top 48 * from Posts
Without the ORDER BY, it’s completely different story. But since it’s so trivial, I assume that Jeff just omitted it for clarity.
What I don’t get mainly is the following. According to the Jeff's measurements, doing something like:
declare @ids table (id int PRIMARY KEY);
insert into @ids (id) select top 48 * from Posts order by SomeField;
select * from Posts in @ids;
should take the 404 ms. But, as I said before, it’s likely that SQL Server uses something along these lines (or pehrpas something more efficient) when executing this:
select top 48 * from Posts order by SomeField;
So there must be something fishy in the measurements:
How was the timing done? In SQL? In a client app?
What the time actually is? CPU? Entire query?
Jeff, did you make sure you used DBCC DROPCLEANBUFFERS before every test?
I would be also good to see query plans. I’m sure the explanation of this issue must be something simple.