SQL SERVER – SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query

A SET ROWCOUNT statement simply limits the number of records returned to the client during a single connection. As soon as the number of rows specified is found, SQL Server stops processing the query. The syntax looks like this:SET ROWCOUNT 10SELECT *FROM dbo.OrdersWHERE EmployeeID = 5ORDER BY OrderDateSET ROWCOUNT 0
To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

When does it work?
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

How does it work?
Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers and data modification statements such as INSERT, UPDATE, and DELETE. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution and primarily with the SELECT statement. The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

Interesting Facts:
Though SET ROWCOUNT n cannot be used in a UDF, the current ROWCOUNT limit setting of its caller will be applied to the SELECT statements in the UDF. When SET ROWCOUNT n applies to a SELECT statement with sub-queries, the results and ordering are always guaranteed. To avoid confusion and unexpected logical errors, it’s better to turn SET ROWCOUNT n on just before the final SELECT statement that returns the records.

SQL SERVER 2005
In SQL server 2005, SET ROWCOUNT n has the same behavior as SQL server 2000. It’s recommended to use TOP (n) instead of SET ROWCOUNT n.

25 thoughts on “SQL SERVER – SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query”

Hi,
I have a quick quesiton for you.
To dynamically select the number of records from a table, I can use a variable and that variable can use used in TOP or SET ROWCOUNT. My question is what if I need to select all the records using TOP statement. I try to explain it with the example below.

DECLARE @resultCount INT
SELECT @resultCount = 0

SET ROWCOUNT @resultCount
SELECT * FROM tblMyTable
SET ROWCOUNT 0

This query will return my all the result from the table. BUT when I do

Can I limit the number of rows returned in a tab or session including all select statements?
For Ex; I want to limit number of rows to 100 in a session , I may have more than one select statement. Is there any solution for this or jst coding my subtracting @@rowcount.

y Can I limit the number of rows returned in a tab or session including all select statements?
For Ex; I want to limit number of rows to 100 in a session , I may have more than one select statement. Is there any solution for this or jst coding my subtracting @@rowcount.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

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