Open In App

SET ROWCOUNT Function in SQL Server

Last Updated : 10 Feb, 2022
Comments
Improve
Suggest changes
Like Article
Like
Report

The ROWCOUNT Set Function causes the server to stop the query processing after the specified number of records is returned. One may limit the number of records returned by all subsequent SELECT statements within the session by using the keyword SET ROWCOUNT. Or we can say that this function causes Transact-SQL statements to stop processing when they have been affected by the specified number of records. This includes triggers. If the rowcount has a smaller value then, it will override the SELECT statement and TOP keyword. Also, it is used to set at execute or run time and not at parse time. This function affects all the statements present in the current database session until the next SET ROWCOUNT function is arrive or until the session is terminated.

Syntax:

SET ROWCOUNT { number or variable }

If the value of SET ROWCOUNT is set to zero then that means we turn off this feature.

Example 1:

The following table contains the details of the product:

ProductIDProductNameSupplierIDCategoryID
1Azithral11
2Augmentin11
3Ascoril12
4Azee22
5Alegra22

Now we use the following query to get the first three rows of the table:

SET ROWCOUNT 3;

SELECT * FROM products;

Output:

Example 2: 

The following table contains the details of the doctors:

DoctorIDDoctorNameTiming
102Aman10 PM
103Mohit11 PM
104Rohit9 AM
105Sumit10 AM
106Anamika2 PM

Now we use the following query to get the first four rows of the table:

SET ROWCOUNT 4;

SELECT * FROM products;

Output:


Next Article

Similar Reads