Paging results in SQL Server 2000

Question

Sometimes I need to make queries in SQL Server 2000 and paginate the results but there is only the clause TOP that limits the number of records returned without an interval (offset) other banks like Mysql or Postgresql have it and query would be like this:

SELECT * FROM tabela LIMIT 0, 50
ou
SELECT * FROM tabela LIMIT 50, OFFSET 0

How can I get around this problem or emulate this feature in SQL Server 2000.

Answer 1

First of all I would recommend a nice upgrade, however there is a way to induce to do something similar

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

In that article of the SOEN in a much more complete way, it shows other possibilities, besides giving a more detailed explanation

Answer 2

The query below has the same result of the "limit 50, 100", that is brings between the record 51 until the 100.

SELECT TOP 50 *
FROM tabela 
WHERE not ID in ( 
  SELECT TOP 50 ID
  FROM   tabela
  ORDER BY ID ASC
) a 
ORDER BY ID ASC;