Use TOP instead of SET ROWCOUNT


October 7, 2013 by Kenneth Fisher

Both TOP and SET ROWCOUNT are both valid methods of limiting the result sets from a query. They are however very different commands. The TOP clause of a command limits that single command, while the SET ROWCOUNT command limits all eligible queries within the connection until another SET ROWCOUNT is called.

If you have several queries where you only want the top 10 rows it might seem better to use SET ROWCOUNT.


SELECT * FROM HumanResources.Department
SELECT * FROM HumanResources.Employee
SELECT * FROM HumanResources.EmployeeDepartmentHistory
SELECT * FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.JobCandidate
SELECT * FROM HumanResources.Shift



SELECT TOP 10 * FROM HumanResources.Department
SELECT TOP 10 * FROM HumanResources.Employee
SELECT TOP 10 * FROM HumanResources.EmployeeDepartmentHistory
SELECT TOP 10 * FROM HumanResources.EmployeePayHistory
SELECT TOP 10 * FROM HumanResources.JobCandidate
SELECT TOP 10 * FROM HumanResources.Shift

So why would I say use the TOP command instead of SET ROWCOUNT? Well among other things because it is more predictable. SET ROWCOUNT limits ALL eligible queries. That means Triggers also.

Here is an example of what can happen using AdventureWorks2008. I’m creating a trigger that will act like a cascading delete on a parent child relationship.

-- Setup Code
-- Create temporary tables to test with to avoid the existing foreign keys
-- Create a temp SalesOrderHeader table
SELECT TOP 20 * INTO TempSalesOrderHeader FROM Sales.SalesOrderHeader
-- Create a temp SalesOrderDetail table
SELECT * INTO TempSalesOrderDetail 
FROM Sales.SalesOrderDetail
WHERE SalesOrderId IN (SELECT SalesOrderId FROM TempSalesOrderHeader)
-- Create Trigger
CREATE TRIGGER dbo.tr_dl_SalesOrderDetail
ON TempSalesOrderHeader
	DELETE FROM TempSalesOrderDetail
	FROM TempSalesOrderDetail
	JOIN deleted
		ON TempSalesOrderDetail.SalesOrderId = 
-- Run my delete using SET ROWCOUNT

DELETE FROM TempSalesOrderHeader

-- Show orphaned OrderDetail rows
FROM TempSalesOrderDetail
WHERE SalesOrderId NOT IN (SELECT SalesOrderId FROM TempSalesOrderHeader)
-- Cleanup code
DROP TABLE TempSalesOrderHeader
DROP TABLE TempSalesOrderDetail

The SET ROWCOUNT caused the delete of the Header table to only delete 10 rows. This was expected. Unfortunately it also caused the delete of the Detail table to only be 10 rows. This is probably not an expected behavior. Note that if I had used a TOP on my DELETE statement I would have deleted all 109 child rows.

Now BOL has said for the last few versions that SET ROWCOUNT will no longer be supported by DELETE, INSERT, and UPDATE statements. It still works with 2012 and I haven’t been able to check on 2014 yet but at some point this is going to happen. Once it does my above example is no longer going to be valid. Of course at that point the DELETE statement is no longer going to be limited either. And in general if you have written code that relies on SET ROWCOUNT to limit a DELETE, INSERT or UPDATE statement then your code will no longer work the way you expected.

I have to admit, if you are very careful, and are the only person writing T-SQL in your system so that no one changes anything behind your back then my above arguments aren’t going to really be that convincing.

Unfortunately for this next bit I wasn’t able to come up with a good example but according to BOL (Under TOP – Best Practices) SET ROWCOUNT is not used by the optimizer to improve the performance of the queries affected by it. Specifically BOL says:

As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be considered in a query plan.

This means that the optimizer may optimize your query as if it was going to pull all 100mil rows instead of just the first 100 that you really wanted. This can result in a wildly different execution plan causing your query to take longer to pull that measly 100 rows that it should.

To be fair I’ve used SET ROWCOUNT plenty of times in my life, although after having read this myself I may cut back some. It does have its but in my opinion only in the simplest most controlled settings and even then probably only for ad hoc queries. SET TOP is just easier to control, has more options (percentages for instance) and is just as easy to put in my code.

8 thoughts on “Use TOP instead of SET ROWCOUNT

  1. David Conlan says:

    Interesting article and very worthwhile knowledge of potentially unexpected behaviour.

    However, as long as the programmer is assured no nasty side effects will occur, SET ROWCOUNT can be useful where the number of rows to be limited is determined by a variable or parameter. TOP does not have this flexibility, as only constants are permitted.

    • There are absolutely times when using SET ROWCOUNT is the way to go. Mostly I just think you should be exceptionally careful when using it.

      However you can use variables with top. Here is a quick example. (Tested on 2005 and 2012)

      DECLARE @toptest int
      SET @toptest = 10
      SELECT TOP (@toptest) * FROM sys.tables
      • David Conlan says:

        Apologies for my misunderstanding and thanks for putting me right. I had tried using TOP with a variable and failed. I wasn’t aware that the variable had to be enclosed in brackets.

        • No problems. The ()s are actually a standard part top. They are just optional if you put in a hard coded value. Also bonus with TOP you can do percentages as well as hard amounts. 🙂

  2. […] Use TOP instead of SET ROWCOUNT – Performing a functional comparison of these two operators and why it’s important to understand their differences, with Kenneth Fisher (Blog). […]

  3. spaghettidba says:

    Nice writeup. thanks for sharing.
    I remember there was a nasty bug with SET ROWCOUNT in SQL Server 2000 that was fixed in SP3 and it drove us crazy.

  4. […] that display information one page at a time. The first 20 rows is pretty easy. That’s just a TOP operation. It gets considerably trickier when you want that second or third group of […]

  5. […] Now normally FizzBuzz is done with a loop, but as Russ said, we are using T-SQL so batch code is always the goal. That said, what table should I query to get the numbers 1-100? Well, I decided I’d just do something simple here and use a system view that’s more than 100 rows, the ROW_NUMBER function, and restrict it using TOP. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: