Differences between TOP and SET ROWCOUNT

Query Execution and Persistence

SET ROWCOUNT causes the Microsoft SQL Server to stop processing the query after the specified number of rows are returned. For example:

SET ROWCOUNT 4;

SELECT *

FROM Production.ProductInventory

WHERE Quantity < 300;

UPDATE Production.ProductInventory SET Quantity = 500

WHERE Quantity < 300;

SET ROWCOUNT 0;

Note: Even though there are more rows that meet the WHERE condition, the SELECT and UPDATE queries in the example above affect only four rows.

Conversely, TOP limits the rows returned in a query result set to a specified number of rows or percentage of rows. Engineering must use TOP in each INSERT, UPDATE, DELETE statement where SET ROWCOUNT was being used because of the difference in the execution of queries. When you have SET ROWCOUNT, this will persist for the life of the connection before resetting it to 0, but TOP does not have that persistence. So all the select, insert, delete, and update queries can follow for the same number of rows to be affected. For example,

SELECT TOP (4) *

FROM Production.ProductInventory

WHERE Quantity < 300;

UPDATE TOP (4)Production.ProductInventory SET Quantity = 500

WHERE Quantity < 300;

Note: You can eliminate SET ROWCOUNT when you use TOP in each query. Engineering must use TOP on each individual query because TOP only affects the single query to which it is applied.