SYSK 275: Pagination in SQL – Easy!

Unfortunately, it’s still a common case that the pagination logic resides in UI… Why I say ‘unfortunately’? Because by making this design choice, you have:

1. Used up bandwidth transmitting the data that may never be seen by the end user.
2. Potentially, are not achieving best performance & scalability, especially in web applications.
3. Using more memory resources than necessary.

 

Why are developers choosing to do pagination using the Grid’s pagination functionality? In many cases, it’s because it’s easy.

 

Well, the stored procedure below demonstrates in just a few lines of code that it’s quite easy to do pagination in SQL Server and, thus, only return the data requested by the user.

 

The code below uses Production.Products table of AdventureWorks database.

 

Note: I’ve limited the returned columns to ProductID, Production.Product.Name, ProductNumber, Color, and ListPrice, but you can easily extend this stored procedure to pass in the data columns to be returned).

 

Also, to minimize the page load time, implement a “look-ahead” logic by getting one extra page that user is likely to navigate to (e.g. next page) by getting it in a background thread, and, to minimize the number of round trips to SQL server, implement middle tier caching retaining in memory a few (e.g. 5) previously retrieved pages of data.

 

ALTER PROCEDURE [dbo].[get_Products]

      @PageNumber int = 1,

      @PageSize int = 0,

      @OrderBy nvarchar(1024) = N'ProductID',

      @SortAsc bit = 1

AS

BEGIN

      -- Usage Sample:

      -- 1. Sort by ListPrice in descending order and return second

      -- page with 5 records per page:

      -- get_Products 2, 5, N'ListPrice', 0

      -- 2. Return all rows sorted by ListPrice in

-- descending order and

      -- get_Products 1, 0, N'ListPrice', 0

      IF (@SortAsc = 1)

            SET @OrderBy = QUOTENAME(@OrderBy);

      ELSE

            SET @OrderBy = QUOTENAME(@OrderBy) + ' DESC';

      IF (@PageSize = 0)

            EXEC ('SELECT ProductID, Production.Product.Name, ProductNumber, Color, ListPrice FROM [Production].[Product] ORDER BY ' + @OrderBy);

      ELSE

            BEGIN

                  DECLARE @SQL nvarchar(4000)

                  SET @SQL = N'SELECT TOP (' + cast(@PageNumber * @PageSize as nvarchar(20)) + N') ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowNumber, ' +

                              N'ProductID, Production.Product.Name, ProductNumber, Color, ListPrice INTO #temp FROM Production.Product';

                  SET @SQL = @SQL + '; SELECT * from #temp WHERE RowNumber > ' + cast(((@PageNumber - 1) * @PageSize) as nvarchar(20));

EXEC (@SQL)

            END

END

GO

Comments

• Anonymous
  January 26, 2007
  It looks like the sample concatenates an SQL query before executing it. This may not be best for preventing SQL injection attacks.

• Anonymous
  January 26, 2007
  That's why the only passed in text field @OrderBy is surrounded by QUOTENAME :)

• Anonymous
  January 26, 2007
  it seems you missed EXEC in ELSE :)

• Anonymous
  January 26, 2007
  Sure did...  Thanks for letting me know -- fixed now!  You certainly merit the name 'грамотей' :)

• Anonymous
  January 26, 2007
  Thanks for the pointer on QUOTENAME.

• Anonymous
  April 15, 2007
  The comment has been removed