SQL Server 2k5 "SELECT TOP" Bug

Beware! SELECT TOP is BUGGED!

"Yes it is true. And this bug has just been discovered by me and confirmed by the Query Optimizer Project Lead (Conor Cunningham) himself, that i have the chance to meet at SQL PASS 2005.

He has been very kind and after I exposed the problem to him, after a little bit of suprise :-), in few days he confirmed that in some circustances SET ROWCOUNT, which is deprecated, is faster than the usually better SELECT TOP.

You can test it yourself, downloading the script right here. I must say that the problem only shows when you need to limit the rows returned by your query while you're sorting over a colum with no index. At least this is my experience. Fortnately this is really an uncommon scenario so the bug is not really a tremendous one, but ouy should better know it since using SET ROWCOUNT under this cirumnstances can speed up your query by more that 3/4 times!

I've been also confirmed that this bug will not be fixed in the forthcoming version of SQL Server 2005 but will be fixed in the first service pack. ..."

An interesting bug.

Given the reported speed impact, this is something I need to look into when we move to SQL 2k5... (Which will be agressive as there are some features in SQL 2k5 that I can use ASAP).