SQL Reference - ROWCOUNT

ROWCOUNT

ROWCOUNT in SQL is a function that is used to limit the number of rows affected by a query. It is specifically useful in procedures and triggers. The function is set with an integer number until the specified number of rows is affected. It always counts the number of rows affected and if the number of affected rows reaches the number set in ROWCOUNT, the query execution stops.

Example

SET ROWCOUNT 5;
SELECT *
FROM Products;

Output

ProductID   ProductName   SupplierID   CategoryID   QuantityPerUnit   UnitPrice   UnitsInStock   UnitsOnOrder   ReorderLevel   Discontinued
1           Chai          1            1            10 boxes x 20 bags   18          39             0             10             0
2           Chang         1            1            24 - 12 oz bottles   19          17             40            25             0
3           Aniseed Syrup 1            2            12 - 550 ml bottles  21           13             70            25             0
4           Chef Anton's Cajun Seasoning 2            2            48 - 6 oz jars     22           53             0            0              0
5           Chef Anton's Gumbo Mix      2            2            36 boxes           21.35        0             0            0              1

Explanation

In the SQL code example, the SET ROWCOUNT 5; statement is limiting the number of rows returned by the SELECT * FROM Products; statement to 5 rows. This allows users to limit the results of their queries to a manageable number.