A common requirement when accessing a SQL Server database is to limit the number of rows returned. There are several ways to achieve this, but many people are unaware of the commands. This short and sweet article will hopefully help out those unfortunate souls and make their lives easier.

For plain SQL queries (i.e. SELECT col1, col2, col3 FROM table), we can use the TOP keyword. By adding "TOP {n}" (where n is some integer value) right after SELECT, we can limit the number of rows we get to the first n rows. Note that you can grab the last n rows simply by adding an ORDER BY statement and reversing the order of the query. Here is an example query:

SELECT TOP 20 last_name, first_name, email
FROM t_user
ORDER BY last_name, first_name

So, for quick queries using Query Analyzer, this is a great little tip to know. Of course, since none of you reading this ever puts raw SQL statements in your ASP code (right?), this really doesn't help much with actual ASP/ASP.NET applications. For those, naturally, we limit ourselves to using Stored Procedures (or at least we would in a perfect world), and so we need a way to achieve this same functionality using sprocs.

Life would be far too simple if you could just add a parameter "@topN int" to your stored procedure, and then in the body of the procedure say "SELECT TOP @topN last_name, first_name, email ..." -- but you can't. No such luck. For some time, this had me pretty much stuck, and I had to resort to using a cursor or dynamically building my query expression in my sproc (both sub-optimal solutions) to achieve this functionality, but then one day on a listserv I saw an example from Bob Levittan on how to do this using a much easier technique, which is the whole reason that I'm writing this article. This is really cool.

SQL Server lets you set a limit on how many rows are to be returned by a query through the use of an environment variable called ROWCOUNT. Thus, in order to achieve the desired result, we simply set this value to the number of rows we want returned. The really cool thing is, by setting this variable to 0 (zero), it effectively removes all restrictions (so queries will return all matching rows). This makes it very easy to add a @maxrows variable to EVERY stored procedure that returns a result set without impacting our applications at all. Since we declare the parameter with a default value of 0, if it is not sent, the sproc behaves just as it did before we added the parameter. You can add this to your existing sprocs and use it as needed without breaking your existing code! Now for that example (borrowed from the IBuySpy Portal application):

CREATE PROCEDURE GetContacts
(
  @ModuleID int,
  @maxrows int = 0
)
AS
  SET ROWCOUNT @maxrows
  
  SELECT 
    ItemID,
    CreatedDate,
    CreatedByUser,
    Name,
    Role,
    Email,
    Contact1,
    Contact2
  FROM
    Contacts
  WHERE
    ModuleID = @ModuleID

That's it! Hopefully this helps a few of you with this relatively common problem -- it certainly performs much better than pulling back a recordset or dataset of ALL rows and then only using the first 10 or so (I know, you'd never do that... but I have and this is much better!).