Here's the most efficient way that I can think of to return rows @a through @b from a table or query, when each row is ordered and can be uniquely identified by a set of columns.
In this example, we have a table called “Data”, and we wish to sort by TransDate. The primary key of the table is “ID” (yep, an identity !) so we include that as the secondary sort to ensure all rows can be uniquely identified by the sort columns.
The plan: set the rowcount to the starting row number (in this case, @a), and get the value of the “starting points” for each sort column. Then, set the rowcount to the number of rows to return (@b-@a), set up a WHERE clause so that we start at the starting point, and then return the results.
This avoids temp tables and always processes as few rows as possible -- at most, @b rows. So it will get slower as you go. But, it works pretty well so give it a shot. If your ordering needs to be dynamically assigned, then this technique may not do much for you but it may help give you some ideas.
Update #1 (3/22/04) : I have slightly edited the code to fix the "first row" error, as specified in the comments.
Update #2 (4/8/07): If you are using SQL Server 2005, it is much easier ... see this.
-- for each column in your sort, you need a variable to hold
-- the "starting values". In our case, we need two:
declare @startingDate datetime;
declare @startingID int;
-- again, we want to returns results from row @a to row @b:
declare @a int;
declare @b int;
set @a = 200 -- start at row 200
set @b = 250 -- end at row 250
-- get the starting date and starting ID to return results:
set rowcount @a
select @StartingDate = TransDate, @startingID = ID
order by TransDate ASC,ID ASC
-- find out how many rows to return, and set the rowcount:
set @b = 1 + @b - @a
set rowcount @b
-- now return the results:
select * from Data
TransDate > @StartingDate OR
(TransDate = @StartingDate AND ID >= @StartingID)
order by TransDate ASC, ID ASC
-- clean up:
set rowcount 0
The WHERE clause in the final SQL statement is the key, and it is good to be familiar with this type of clause even if you don't use this exact paging technique. Look at it closely, and you will see it expresses logic to return rows within a certain range when that range is defined by more than 1 column.
For example, if you have a column called “YearMonth” with values in a YYYY-MM format, you can easily say:
WHERE YearMonth >= '2003-04'
But if you have two separate columns, one for Month and one for Year, you would express that same WHERE clause this way:
WHERE Year > 2003 OR (Year = 2003 AND Month >= 04)