Number of Rows Affected Rowcount and Top keyword

Hi,

Many a times in a query we need to limit down the number of records returned in the query. This can be done in two ways using the TOP keyword or the row count keyword.

Select top 10 from Table

This will return the top 10 records from the query. We can also make this dynamic by making the Value in Top keyword to come from a variable.

DECLARE @top INT
SET @top = 10
SELECT TOP(@top) * FROM Table

If using a stored procedure we can also pass this variable as a parameter in the procedure. This way we can easily control the number of record affected.

Another way to do the same stuff is to use the rowcount variable.

DECLARE @top INT
SET @top = 10
SET ROWCOUNT @top
SELECT * FROM Table
set rowcount 0

Remember converting the rowcount back to 0 is very important; otherwise all other query will follow the same rowcount as set earlier.

The drawback with rowcount is that in the future version this will not work with the delete and update statement in the future version of the SQL server, so its best to use the top keyword only.