Posts
49
Comments
50
Trackbacks
26
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.

So, as we say in Itay: "Man advised man half saved" :-)

 

posted on Friday, October 21, 2005 1:35 PM Print
News