Use of set rowcount option in SQL Server

Set Rowcount: - This set statement is used to restrict SQL Server to process the query after the specified no of rows has been returned by the query.

Set Rowcount 5à SQL Server will stop processing the query once first 5 rows has been returned by the query.

Set Rowcount 10à SQL Server will stop processing the query once first 10 rows has been returned by the query.

To understand this let’s take an example of setting up Rowcount option.

I have created a table “Employee” and inserted 10 rows into it.

create table Employee(Eid int, Name varchar(50))

insert into Employee values(101,'Neeraj')

insert into Employee values(102,'Neha')

insert into Employee values(103,'Rachit')

insert into Employee values(104,'Akshay')

insert into Employee values(105,'Ankit')

insert into Employee values(106,'Gaurav')

insert into Employee values(107,'Prashant')

insert into Employee values(108,'Tarun')

insert into Employee values(109,'Vidushi')

insert into Employee values(110,'Pranav')

If I write below query, Lets see what will be the outcome.

select * from employee

Employee table is containing 10 rows into it and outcome of the query would be all 10 rows.

Now we will  use set rowcount statement before the query and execute it , let’s see what comes as an outcome.

Set Rowcount 4

select * from employee

In the result set we can see that only 4 rows has been returned by the query even the table has 10 rows in it and the reason is that SQL Server stopped processing the query once the first 4 rows has been returned by the query.

Important Points to Remember:-

• Set rowcount statement also works in delete and update statements in the same way that it works with select statement.
• Setting up rowcount value only applies to the current session not for all the user connections(session).
• By default Rowcount value is 0 that means there is no restriction on the execution of the query.