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 10
SELECT *
FROM dbo.Orders
WHERE EmployeeID = 5
ORDER BY OrderDate
SET 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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

    DECLARE @resultCount INT
    SELECT @resultCount = 0

    SELECT TOP (@resultCount) * FROM tblMyTable

    I don’t get any results back.

    How can I use TOP to get all the results

  2. Hi Dave,

    It is very useful site to solve most of the complex requirements.

    I have one more question… how to delete monthwise ten days ten days records if it is a large table ,

    Thanks
    G Arun

  3. how we can stop this SET ROWCOUNT command?, I want to know about the command which is necessary to stop the functioning of SET

    • You can use TOP operator. Also SET ROWCOUNT will not be supported in future version so better make use of TOP operator

  4. thanx, but I am saying that if a SET command is running on my SQL Server screen than how I can stop its work which that command is performing……..
    I need a syntax querry to unset a SET command……

  5. I have one small question it is session based query or not.
    I am using SQL Server2008R2 and if i run this query in one tab its only working in the same tab.
    its not working on another tab.

    For example:-
    1. First Tab
    set rowcount 10
    &
    select * from Table_Name

    its shows 10 records only

    2.Second Tab

    select * from Table_Name

    Its show all records of table

    Note :- Same DB use

  6. User enter how many rows he want to see, If he enter zero then I want to display zero rows,

    How can I do this using set row count, Is there other optimized way to do.

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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