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?
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)
28 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
Top with variable will work from version 2005 onwards only
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
Can you give more inofmrations on what you want to do?
Add
SET Top@resultCount
what if i want to retrive last n records and order by time????
select top n * from your_table
order by time desc
Hi,
Can u tell me the query to get records from 15 to 100?
You need to use pagination. Refer this post to know how you can do it by using row_number() function
i have a doubt can we change the name of the column
Yes using the sp_rename procedure. But make sure that it doesn’t affect the existing codes
use something like-
please check the exact syntax–
sprename ‘tablename.oldcolumnname’,’newcolumnname’,’column’
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
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……
Use this
SET ROWCOUNT 0
at the end of the code
thanx, madhivanan for providing this information to me, its usefull for me
how we can attach an ms-excel file to our database table through import or export?
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
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.
If the rowcount is 0 then just add a dummy condition 1=0 in the where clause otherwise remove it
Also you can use TOP operator instead of SET ROWCOUNT and TOP 0 will get you 0 rows
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.
thanks a lot….i got a lot of idea with your help..
I am glad it helped you Suman. Thanks for letting me know.
Is there any way to get rows affected(individually) for multiple dynamic queries.
Ho. I have a problem. How can I use rowcount for a big update so can I update in blocks of 10000 rows in loop?
Thansk