Using an arbitrary TOP value in SQL Server 2000

This tip was submitted to the SearchSQLServer.com tip library by Serdar Yegulalp. Please let others know how useful it is via the rating scale at the end of the tip. Do you have a useful tip, timesaver or workaround? Submit it today!

In T-SQL, the TOP keyword is used to return a fixed number of records in a query:

SELECT TOP 50 [ID] FROM OurUsers

This query would return only the first 50 matches; everything after that would simply not be returned.

TOP is useful for limiting the scope of a query. For instance, you may return paginated results, but how useful they are may be limited by the fact that they can't accept a variable in the context of a stored procedure. For instance, this would not be a legal statement, where @ROWS is a parameter passed in the context of a stored procedure

SELECT TOP @ROWS [ID] FROM OurUsers

SQL Server 2005 apparently will allow variables to be used as an argument for TOP -- but what if you have no plan to upgrade? To get around this limitation, depending on the scope of what you're trying to do, here are some options:

1. Create a dynamic SQL statement rather than use a stored procedure: This statement could be passed from a program (such as an ASP script) or generated dynamically within a stored procedure. But performance is the downside of this option; each dynamic SQL statement has to be syntax-checked and compiled before running. If performance is not the biggest consideration, this is easy enough.

2. Use the SET ROWCOUNT command: SET ROWCOUNT works like TOP in that it limits the number of records returned. The difference between the two is that SET ROWCOUNT affects everything in the current connection, but, more importantly, it accepts a variable. A sample script might work like this:

SET ROWCOUNT @ROWS
SELECT [ID] FROM OurUsers
SET ROWCOUNT 0

The ROWCOUNT 0 statement returns things to normal so future queries passed in the same connection aren't subject to the same stricture. If you're working with a derived query, you can insert the ROWCOUNT-constrained results into a temporary table, which can then be dropped when you're done. (In a stored procedure, a temporary table would be dropped automatically, which is even more convenient.)

Do you have comments on this tip? Let us know.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!