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:
- Used up bandwidth transmitting the data that may never be seen by the end user.
- Potentially, are not achieving best performance & scalability, especially in web applications.
- 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