A Good use of RowCount in SQL Server 2005 and 2008

If you are in a situation where you want to limit the number of rows return for a given query just use Rowcount. Easy!

For example you have a scenario where you want only to show the 8 most recent orders out of 25 orders. So you can just set the rowcount to 8 and this is what will be returned.

NOTE: Then don't forget to set the RowCount back to 0 to reset it.

SET ROWCOUNT 3
-- Get list of orders
SELECT OrderID, DateCreated, DateShipped,
Verified, Completed, [Cancelled], CustomerName
FROM Orders
ORDER BY DateCreated

In this SQL query above, only the 3 records will be returned. And then to reset the RowCount just type:

Set ROWCOUNT 0

This will reset the ROWCOUNT for the query.

Easy isn't? Just try it!

Cheers!