Open In App

SET ROWCOUNT Function in SQL Server

Improve
Improve
Like Article
Like
Save
Share
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:

ProductID ProductName SupplierID CategoryID
1 Azithral 1 1
2 Augmentin 1 1
3 Ascoril 1 2
4 Azee 2 2
5 Alegra 2 2

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:

DoctorID DoctorName Timing
102 Aman 10 PM
103 Mohit 11 PM
104 Rohit 9 AM
105 Sumit 10 AM
106 Anamika 2 PM

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

SET ROWCOUNT 4;

SELECT * FROM products;

Output:


Last Updated : 10 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads