rowcount before and after query

开发者_StackOverflow中文版Can someone explain the difference between these 2 simple queries ...

SET ROWCOUNT = 10
select * from t_Account order by account_date_last_maintenance

and this one

select * from t_Account order by account_date_last_maintenance
SET ROWCOUNT = 10

when executed both return only 10 rows, but the rows are different. There are millions of rows in the table if that matters. Also, the first query runs consistently 20% longer.

Thanks everyone

When you execute SET ROWCOUNT 10 you are telling SQL to stop the query after 10 results are returned. Your first SQL statement is the correct syntax (with the exception of the first line which should read SET ROWCOUNT 10).

The second statement as written will return all of the values ordered when initially executed and then set the row count to 10, so any subsequent execution will return the first 10 items.

ROWCOUNT must be set to 0 to get things back to "normal" execution.

As to why things were returning differently, the data might not be processed the same every time and given the size of your dataset it is most likely that you might sometimes get matching results, but it is not a sure thing. If you want consistent results and only want the first 10 results I would recommend using TOP.