SET ROWCOUNT Function in SQL Server
Last Updated :
10 Feb, 2022
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:
Share your thoughts in the comments
Please Login to comment...