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.

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

When does it work?
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 row count is the smaller value.

How does it work?
Setting the 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 this command 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, this command has the same behavior as SQL server 2000. It’s recommended to use TOP (n) instead of this command.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 Security DataSheet
Next Post
SQL SERVER – Collate – Case Sensitive SQL Query Search

Related Posts

27 Comments. Leave new

  • 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

    Reply
  • 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

    Reply
  • Add

    SET Top@resultCount

    Reply
  • what if i want to retrive last n records and order by time????

    Reply
  • Hi,

    Can u tell me the query to get records from 15 to 100?

    Reply
    • You need to use pagination. Refer this post to know how you can do it by using row_number() function

      Reply
  • i have a doubt can we change the name of the column

    Reply
    • Yes using the sp_rename procedure. But make sure that it doesn’t affect the existing codes

      Reply
    • use something like-
      please check the exact syntax–
      sprename ‘tablename.oldcolumnname’,’newcolumnname’,’column’

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

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

      Reply
  • 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……

    Reply
  • thanx, madhivanan for providing this information to me, its usefull for me

    Reply
  • how we can attach an ms-excel file to our database table through import or export?

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • thanks a lot….i got a lot of idea with your help..

    Reply
  • Is there any way to get rows affected(individually) for multiple dynamic queries.

    Reply

Leave a Reply

Menu