SET ROWCOUNT limits the session, while TOP limits a statement. I reset ROWCOUNT after testing so later queries keep their intended limits.
The original comparison contained two conflicting statements about which limit wins. The smaller active ROWCOUNT limits the SELECT. TOP does not automatically cancel that session setting.
DECLARE @Numbers TABLE (n int PRIMARY KEY);
INSERT INTO @Numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
SET ROWCOUNT 5;
SELECT TOP (10) n FROM @Numbers ORDER BY n;
SET ROWCOUNT 0;
SELECT TOP (10) n FROM @Numbers ORDER BY n;The first result contains 1 through 5. After SET ROWCOUNT 0, the second contains 1 through 10. Reset the setting after a test; it can affect later work in the same connection.
Choose rows with an explicit order
ORDER BY makes the chosen values predictable in this example. Without an order, TOP limits the count but does not promise which rows are returned. A unique tie-breaker matters when sorting values that repeat.
I prefer TOP or OFFSET/FETCH for new statement-level limits. Microsoft advises against using SET ROWCOUNT to limit INSERT, UPDATE or DELETE. That DML behavior is scheduled to change.
ROWCOUNT doesn’t restrict dynamic cursors. It does limit the rowsets of keyset and insensitive cursors. The setting takes effect at runtime rather than parsing.
Reference: SET ROWCOUNT.
A row limit is not an ordering rule, it is a limit on how many rows can return.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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