Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

(From Jeff's SQL Server Blog:
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx)
(UPDATE: If you are using SQL Server 2005 or above, there is a much easier and more efficient way to page your data.)

Here is a completely dynamic version of my paging technique in the form of a stored procedure. Just pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset. The stored procedure then will process your SQL in the order specified and return only the rows indicated. It is based on the same principals I presented in my blog several months ago, in which the paging technique is described.

The basic idea is to process as few rows as possible; this means finding the starting point, and then returning all rows “past“ that starting point until the desired number of rows has been returned.

The stored procedure is called “ReturnPage“ and uses the following arguments:

* @Select = the select statement to return
* @OrderBy = the order by clause; don't include the “ORDER BY“ part, just the columns. You must include ASC or DESC for each column in the sort
* @StartRow = the first row to return
* @EndRow = the end row to return

A minor tweak in the code could allow for a @RowCount argument instead of @EndRow if you wish.

Let's start with some examples from northwind:

returnpage 'select contactTitle, City, CustomerID from customers', 'ContactTitle ASC, City DESC, CustomerID ASC', 1, 10

returnpage 'select * from Orders','EmployeeID ASC, OrderDate DESC, OrderID ASC',12,31

returnpage 'select * from [order details]','productID ASC, Quantity DESC, OrderID asc',30,45

And a couple of caveats / notes about this procedure:

* First off, it is still in beta testing so use at your own risk. Please give me some feedback !
* As written, the ASC/DESC designation is required for each column in the @OrderBy argument, unlike in a regular SELECT, in which ASC is the default
* Currently, all column names in the @OrderBy argument must consist of only 1 word; even using [ ] around a column name will not work. I didn't feel like spending too much time on the parsing routine, but feel free to improve upon it in your own implementation.
* Feel free to print out the @SQL statement generated by the routine to see how it works
* As a reminder, a requirement of my algorithm is that the sort columns must form a unique constraint for the resultset; you cannot sort only by “Customer Name“ if it is not always unique for each row -- you would need to include “CustomerID“ as a secondary sort. Notice how I have done this in all of the Northwind examples.
* There should not be any Null values in the @OrderBy columns for this technique to work

I hope you enjoy and it works fine for you. It might seem like this routine is doing a lot of work to parse the arguments and generate the T-SQL to execute on each call, but it is all done on the server and the parsing is extemely quick since it requires no I/O or database access -- it's all just in-memory manipulation of variables.

Anway, like the other paging implementations, it's not perfect, but it is truly a 100% server side implementation and it will process as few rows as theoretically possible for a dynamically sortable paging solution.

Final note: if anyone is interested, please let me know and I can provide some more information about how this procedure was written and how it works. To keep the code somewhat compact, I'm sure it's not very clear. The algorithm to generate the WHERE clause was one of the more challenging ones I've written recently.

The original post describing the paging technique can be found here: http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx



CREATE PROCEDURE ReturnPage(@Select varchar(1000), @OrderBy varchar(1000),

@StartRow int, @EndRow int)

AS

BEGIN



declare @ColList varchar(2000);

declare @Where varchar(2000);

declare @i int;

declare @i2 int;

declare @tmp varchar(1000);

declare @dec varchar(1000);

declare @f varchar(100);

declare @d varchar(100);

declare @Symbol char(2);

declare @SQL varchar(5000);

declare @Sort varchar(1000);



set @Sort = @OrderBy + ', '

set @dec = ''

set @Where = ''

set @SQL = ''



set @i = charindex(',' , @Sort)

while @i != 0

begin

set @tmp = left(@Sort,@i-1)

set @i2 = charindex(' ', @tmp)

set @f = ltrim(rtrim(left(@tmp,@i2-1)))

set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))

set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))

set @i = charindex(',', @Sort)

set @symbol = case when @d = 'ASC' then '>' else '<' end +

case when @i=0 then '=' else '' end



set @dec = @dec + 'declare @' + @f + ' sql_variant; '

set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +

@f + @Symbol + ' @' + @f

set @Where = @Where + ' OR (' + @ColList + ') '

set @SQL = @SQL + ', @' + @f + '= ' + @f

end



set @SQL = @dec + ' ' +

'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +

'SELECT ' + substring(@SQL,3,7000) + ' from (' + @Select + ') a ORDER BY ' +

@OrderBy + '; ' + 'SET ROWCOUNT ' +

convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +

'select * from (' + @Select + ') a WHERE ' +

substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'



exec(@SQL)

END