Database.ca SQL Server Blog

My Blog

Monday, December 02 2013

Keywords: SQL SERVER, TOP, SET ROWCOUNT, ORACLE, ROWNUM

SQL TOP (@n) vs SQL SET ROWCOUNT vs ORACLE ROWNUM

In older versions of SQL Server (2005 and earlier) you could Not use a variable in a TOP statement so ROWCOUNT was one obvious choice. There were other ways of returning the same results, by doing advanced queries, but ROWCOUNT was a built in operator that fitted the purpose right out of the box, and it was faster.

 

Performance differences between TOP (@n) and ROWCOUNT:
 
I ran the following code snippet multiple times for each of the two query types, on a SQL Server 2012, and the conclusion was that there were absolutely no performance differences between the two.

 

SQL SERVER, TOP, SET ROWCOUNT, ORACLE, ROWNUM

 

SQL SERVER, TOP, SET ROWCOUNT, ORACLE, ROWNUM

Sorting:
 
Although Order By clause is not mandatory when using these operators, I would highly recommend you to use one. Otherwise you are basically getting back random records and although the data might look sorted on one specific version of SQL Server running on a specific box, it is Not at all guaranteed to behave that way on a different version or machine, and thus hidden unexpected bugs might arise one day. The sorting is Not guaranteed unless you use Order By.
 
Also to acknowledge, is the fact that TOP and ROWCOUNT both truncate the already sorted data. Order By occurs first, then data is cut off.
 
Watch out with SET ROWCOUNT’s scope:
 
The scope of SET ROWCOUNT is for the current connection only. This includes procedures called by the current procedure. It could also include dynamic SQL executed within this connection, so be careful.
 
TOP will only effect the query you are running now, while ROWCOUNT will persist with the connection and apply to all queries executed within that connection, unless you put an end to it by using SET ROWCOUNT 0.
 
That fact is a tricky one…
 
Make sure you do SET ROWCOUNT 0 in your error handler as well.

 

TOP has the % advantage:
 
With TOP you are not forced to use an integer, but could also use a percentage.

 

TOP can handle situations where ties in column values occur:
 
By using WITH TIES, you will handle situations where values tie. You cannot do this with ROWCOUNT. Per example “SELECT TOP(3) WITH TIES id from xyz” could return:
 
34
40
51
51
51

 

If you use TOP and ROWCOUNT both:
 
You shouldn’t do that in the first place. You should ideally only use TOP from now on. But assuming you mix the two, ROWCOUNT will override TOP if ROWCOUNT value is smaller.

 

SQL SERVER, TOP, SET ROWCOUNT, ORACLE, ROWNUM

Optimize the TOP:
 
If you use TOP (@n) then you have the luxury of using OPTION (OPTIMIZE FOR (@var= somevalue) ) . Using the OPTIMIZE FOR hint can change the query plan. This may have a positive impact or a negative impact, but this gives you another option to adjust how your queries execute especially if things start performing poorly when using parameters in your queries.

 

Know how Oracle’s ROWNUM behaves!
 
Be careful with Oracle’s ROWNUM, it’s quite sneaky & tricky…and I have to say that its logic is not only different but rather faulty:
 
The row count cut off happens BEFORE the order by clause in Oracle!!! Therefore it will behave very differently from SQL Server…by repeatedly running an Oracle query that uses ROWNUM you could get different results at each run, even though you have an ORDER BY clause!!! To remedy this you would need to have a sub query that applies the ORDER BY, and the outer query will apply there WHERE ROWNUM <= v_maxrecords.

 

They are not only for querying. They also work with DELETE, INSERT & UPDATE & MERGE:
 
I personally advise against using these operators with anything other than SELECT. In different versions of SQL Server, and depending on whether you use TOP or ROWCOUNT, you could end up having different limitations and behaviours and bugs. This gets even worse if you are dealing with DELETE/INSERT/UPDATE triggers etc. If you have to use them, then write thorough unit tests.
 
Be careful using them with cursors, triggers, views, merge, union, except, or intersect:
 
Again I advise that you either don’t use them with the above statements, or otherwise write extremely thorough unit tests.
 
In the case of cursors, you will get different results depending the type of cursor. The ROWCOUNT option does not affect dynamic cursors, but it does limit the row set of keyset and insensitive cursors. This option should be used with caution.
 
Test a lot.
 
Future of SET ROWCOUNT:
 
You’re better off not using SET ROWCOUNT, and start using TOP. It wouldn’t also be a bad idea to refactor your existing code to use TOP. Microsoft has announced that in future versions of SQL Server SET ROWCOUNT will not work with DELETE, INSERT, UPDATE…(it will however be there for SELECT). Use TOP instead.

 

Written by Ramin Haghighat