Limiting rows in Sql Server

One problem I have always faced with Sql Server (more so, after I used MySql, and saw that MySql allows this) is to find a way to limit the number of rows being fetched in a SELECT query.

Although Sql Server provides a ROWCOUNT statement & TOP clause for the SELECT statement, they just provide you an option for specifying the number of rows (as an absolute number or percentage) to fetch from the beginning of the resultset (or from the end, by using a ORDER BY DESC clause).

Both of these options do not provide a true MySql like capability for limiting the number of rows being fetched from the server by their position, between a specified range.

I often found myself writing Stored Procedures that used Table variables with an identity column & a second column, in which the primary keys for the ResultSet would be inserted, by using a

INSERT INTO @tableVariable
SELECT Id FROM Table1

statement. This Table Variable was then used to limit rows by using its Identity column which would be JOINed with the original ResultSet.

Needless to say, this approach required too much plumbing every time I tried to limit rows on the server (Limiting rows on the client is easy after fetching them, but you should remember the performance cost of retrieving the entire ResultSet to the client, when you only need a small subset of it).

Yesterday late night, while working on a project, I struck a new way of limiting rows in a single SELECT statement without using any intermediate steps. Without syaing more, here is what I did:

SELECT Top (@rowsToFetch) *
FROM (SELECT * FROM Table1
               EXCEPT
           SELECT TOP (@beginIndex - 1) * FROM Table1) a;

The Sql for the most part is self explanatory. @beginIndex is the position from where you want the rows to start. @rowsToFetch is the number of rows to fetch (e.g. LIMIT 0,30 in MySql parlance).

Basically we remove the first @beginIndex rows from the ResultSet using en EXCEPT clause, and then fetch the next @rowsToFetch rows.

There are a couple of points you should remember:

• It would be preferable to use the Primary Key column in the nested SELECT..EXCEPT clauses to improve the clause performance.
  This might require a JOIN in the outer SELECT with the Ids fetched in the inner SELECT. I mean the following:

  SELECT Table1.*
  FROM Table1 INNER JOIN
       (SELECT Top (@rowsToFetch) Id
          FROM (SELECT Id FROM Table1
                 EXCEPT
             SELECT TOP (@beginIndex - 1) Id FROM Table1) a) b ON Table1.Id = b.Id;

• The EXCEPT clause might cause performance issues for large tables. However, for smaller ones, the query saves a lot of time in routine plumbing.