Have you ever wanted to return the top X number of rows from a query?  Of course you have!  This can be accomplished easily in T-SQL using the TOP option of the SELECT statement.

SELECT TOP 10
       EmployeeID,
       FirstName,
       LastName
FROM   Employee
ORDER BY
       Utilization

But what if the number of rows you want to return is unknown?  Say, for example, this query is for a report and the end-user gets to specify how many results they want.  Unfortunately, T-SQL does not provide the ability to say

SELECT TOP @n

What do you do?  Do you just return all of the results and let the report do the filtering?  This would result in wasted resources in both returning and then removing (or logic to ignore) the extraneous data.  Do you write dynamic SQL, instead, to get the correct number of rows?  Taking this approach would negate some of the benefits of implementing stored procedures in the first place (user security, injection attacts, etc.).

Fortunately, there is a way to do exactly what we want in our stored procedure without resorting to dynamic SQL or worrying about extra data using the SET ROWCOUNT option!

SET ROWCOUNT causes SQL Server to stop processing the query after the specified number of rows are returned (http://msdn.microsoft.com/en-us/library/ms188774.aspx).

/* Set the number of rows to return (from stored procedure parameter) */
SET ROWCOUNT @numberOfResults

/* Execute our query */
SELECT EmployeeID,
       FirstName,
       LastName
FROM   Employee
ORDER BY
       Utilization

/* Set ROWCOUNT back to 0 to return all rows by default */
SET ROWCOUNT 0

Voila!  The exact number of results we want; no extra processing; no dynamic SQL – a little slice of Heaven!

- E.

Tags: Performance Tuning, SQL, Stored Procedure, T-SQL

This entry was posted on Monday, December 22nd, 2008 at 2:58 pm and is filed under Developer. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.