The SQL ROWCOUNT is a SQL Server SET statement which causes SQL Server to stop processing the query after the specified number of rows are returned.
SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
SET ROWCOUNT does not affect the INSERT, UPDATE, and DELETE.
SYNTAX
SET ROWCOUNT { number | @number_var }
number | @number_var is the number, an integer, of rows to be processed before stopping the specific query.
Lets look at an example of using SET ROWCOUNT in SQL.
First we prepare a sample table named as SalesDetails and insert few records into that table. Following is the scripts.
CREATE TABLE dbo.SalesDetails ( SalesId INT NOT NULL, SalesPerson VARCHAR(50), Product VARCHAR(150), Quantity INT, Price NUMERIC(9,2), [Date] DATE ) INSERT INTO dbo.SalesDetails VALUES (1,'Raj Kishor', 'Samsung Tablet', 5, 75000, '12-09-2019'), (2,'Raj Kishor', 'Samsung Tablet', 3, 41000, '08-12-2019'), (3,'Raj Kishor', 'Iphone 5S', 2, 36000, '05-01-2020'), (4,'Suraj Singh', 'Samsung Tablet', 2, 20000, '07-01-2020'), (5,'Suraj Singh', 'Iphone 7', 2, 55000, '12-24-2019'), (6,'Suraj Singh', 'Samsung NoteBook', 2, 35000, '01-25-2020'), (7,'Raj Kishor', 'Samsung Tablet', 6, 85000, '02-11-2020'), (8,'Mahesh Kumar', 'Iphone 6', 2, 49000, '02-21-2020'), (9,'Mahesh Kumar', 'Iphone 5S', 3, 45000, '03-03-2020'), (10,'Mahesh Kumar', 'Iphone 6', 2, 49000, '03-21-2020'), (11,'Raj Kishor', 'Iphone 6', 3, 65000, '03-25-2020'), (12,'Raj Kishor', 'Iphone 5S', 2, 36000, '03-27-2020'), (13,'Mohit Singh', 'Samsung Tablet', 3, 48000, '02-21-2020')
Now we have a table named as SalesDetails which contains sales details and total number of records are 13, you can check table records and count the number of records as shown below.
SELECT * FROM dbo.SalesDetails SELECT COUNT(*) AS TotalCount FROM dbo.SalesDetails
Using ROWCOUNT in T-SQL
Lets see the ability of ROWCOUNT, suppose you want to limit the number of rows to 10.
So when you set ROWCOUNT to 10, as soon as the number of rows specified that is 10 is found, SQL server stops processing the query, and you can see it returns only 10 records from table while table contains 13 records.
SET ROWCOUNT 10; SELECT * FROM dbo.SalesDetails ;
SET ROWCOUNT to OFF
You can stop ROWCOUNT, by setting ROWCOUNT 0.
As you can see, following T-SQL returns all records from table.
SET ROWCOUNT 0; SELECT * FROM dbo.SalesDetails ;
Using SET ROWCOUNT on TOP Keyword
If you use ROWCOUNT and TOP clause together, in such case ROWCOUNT overrides the TOP keyword, if the rowcount is the smaller value.
Lets take a look on below example, you can see the number for ROWCOUNT is set to 5 which is smaller than the value provide to TOP keyword that is 8, so in this case ROWCOUNT overrides the TOP keyword and limit the rows to 5, and you can see only 5 rows are returned from table not 8.
SET ROWCOUNT 5; SELECT TOP 8 * FROM dbo.SalesDetails ;
Lets take one more example, and this time SET a greater value to ROWCOUNT.
Before that just stop the previous SET values of ROWCOUNT by setting ROWCOUNT 0 otherwise it keeps limiting the rows to 5 (as previously we set it for ROWCOUNT 5) so need to stop it by setting it to 0.
SET ROWCOUNT 0;
Once you execute the above statement, now table will returns all records as ROWCOUNT is stopped.
Now this time we provide a lower value to TOP keyword in comparison of SET ROWCOUNT value.
As you can see in below statement, we have set ROWCOUNT to 9 while fetching top 8 records from table.
Lets execute the statement, and you can see the output, Only 9 rows are returned as ROWCOUNT is set to 9 which is greater than the value fetching by top keyword that is 8. So ROWCOUNT does not override the TOP keyword.
So only top keyword is applied to statement and returns top 8 records from table.
SET ROWCOUNT 9; SELECT TOP 8 * FROM dbo.SalesDetails ;
SQL SERVER SET IDENTITY_INSERT
