Transact SQL Other Articles Software Reviews
Using SET ROWCOUNT to Limit the Number of Rows Returned
This article describes a straightforward method of limiting the number of rows returned in a SQL Server results set by making use of the SET ROWCOUNT statement.
This technique has a number of potential uses, such as displaying the 10 most recent news article headlines on a website.
Although this functionality can also obviously be provided by using SELECT TOP..., setting the rowcount is a useful technique to use when SELECT TOP cannot be used, such as in this example of generating randomly ordered SQL Server result sets, which uses SELECT TOP to select a number of adverts, then limits the rowcount to return a specified number of randomly sorted adverts.
The use of the SET ROWCOUNT statement is shown in the following example for the pubs database. The SQL statements return the first 2 authors who live in the state of California:
DECLARE @NumberOfAuthors int
The number of rows to return can also be specified as a stored procedure argument:
CREATE PROCEDURE sp_GetAuthors
Note that setting ROWCOUNT to 0 will cause all of the rows to be returned.