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



      -- 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);


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


      IF (@PageSize = 0)

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



                  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));









Comments (6)

  1. Doug says:

    It looks like the sample concatenates an SQL query before executing it.

    This may not be best for preventing SQL injection attacks.

  2. irenake says:

    That’s why the only passed in text field @OrderBy is surrounded by QUOTENAME 🙂

  3. gramotei says:

    it seems you missed EXEC in ELSE 🙂

  4. irenake says:

    Sure did…  Thanks for letting me know — fixed now!  You certainly merit the name ‘грамотей’ 🙂

  5. Doug says:

    Thanks for the pointer on QUOTENAME.

  6. Hogan says:

    Two questions:

    1) Why use the temp table?  Can’t you just bound rownumber with both a less than statement and the greater than statement you use – eg

    EXEC(N’SELECT ROW_NUMBER() OVER (ORDER BY ‘ + @OrderBy + ‘) as RowNumber, ‘ +          N’ProductID, Production.Product.Name, ProductNumber, Color, ListPrice FROM Production.Product WHERE RowNumber > ‘ + cast(((@PageNumber – 1) * @PageSize) as nvarchar(20)) + N’ AND RowNumber < ‘ + cast(@PageNumber * @PageSize as nvarchar(20)))

    {I did not test so there might be an off by 1 error}

    2) This does not help in secure and locked down system where the connecting user only has rights to execute stored procedures and does not have SELECT rights on the tables.  Remember, EXEC runs in the context of the connecting user and not in the context of the stored procedure.  

Skip to main content