Pagination in MSSQL 2005

Posted by Dan on Jun 10, 2008 @ 10:03 AM

Paginating data is one of those commonly required tasks. In previous version of MS SQL Server it's been tricky to handle, but made easy via the use of stored procedures that handle the pagination logic for you. However, if you're using SQL Server 2005 there's a much easier way to handle pagination and that's to use a Common Expression Table (CTE.)

Microsoft created the CTE syntax to make solving complicated tasks easier (such as returning a query of hierarchical tree data.) When you create a CTE, you're essentially creating a virtual table you can query against. CTEs generally perform very well and can often replace the need for creating temp tables.

Let's look at an example of query out records 11-20 from a Employee database:

-- create the Common Table Expression, which is a table called "pagination" with pagination as ( -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration select row_number() over (order by department, employee) as rowNo, -- a list of the column you want to retrieve employeeId, employee, department from Employee where disabled = 0 ) -- we now query the CTE table select -- add an additional column which contains the total number of records in the query *, (select count(*) from pagination) as totalResults from pagination where RowNo between 11 and 20 order by rowNo

The first part of the query creates the table expression we'll actually query against when we grab the actual results from the database. In the CTE you're going to write the SQL you'd normally write to grab all the records with one exception—you use the row_number() function to generate a virtual column that orders your result rows for you. The actual "order by" clause you want to use will actually go in the row_number() declaration—this makes SQL Server assign the correct row number for each record.

It's important to remember that a CTE can be as complex as you need it to be. You can do joins, pivots, etc—whatever you need.

The next step is to actually query against the CTE you created. This query will virtually be the same for all pagination queries you write. You are simply grabbing all of the columns and then limiting the returned data to just the rows you want to display (in this case rows 11 - 20.)

In this example, I added an additional column that contains the total results that were returned from the CTE. This is not needed, but it's handy if you need to do something like "Results 11 - 20 of 4,567 records." There are many ways you could generate the total records, this is just one method.

If you've upgraded to SQL Server 2005 and haven't started using CTEs yet, you really need to start getting familiar with them as they really simplify many tasks in SQL Server that previously were very difficult to solve.

Categories: SQL, Source Code

14 Comments

  • Thanks for posting this Dan - it's cool to have another tool in the box for handling this kind of thing. This method does seem to be nice and simple and I like how it can just be thrown into a CFQUERY call.

    Nice and simple syntax.. Cool.
  • Dan,
    To add a GROUP BY clause, where would I add it - to the 'with pagination as' statement or the CTE table query?
  • @James:

    The CTE is where all the core logic would go--so that's where your GROUP BY needs to go. Here's an example of CTEs and the GROUP BY clause:

    http://msmvps.com/blogs/martinpoon/archive/2006/09...

    The following query returns the top 3 sales persons for each product.
    "PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

    SELECT Z.*
    FROM (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
      , C.SalesPersonID
      , A.ProductID
      , ProductTotal = SUM(A.LineTotal)
      , B.ProductNumber
      , ProductName = B.Name
      FROM Sales.SalesOrderDetail AS A
      INNER JOIN Production.Product AS B
       ON A.ProductID = B.ProductID
      INNER JOIN Sales.SalesOrderHeader C
       ON A.SalesOrderID = C.SalesOrderID
      WHERE C.SalesPersonID IS NOT NULL
      GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name) Z
    WHERE Z.RowNumber <= 3
    ORDER BY Z.ProductID, Z.RowNumber

    NOTE: In the example above it's not paginating, but showing the top 3 options for each group.
  • Lovely, thanks Dan.
  • Had not thought about using WITH. Statement to allow you to get the total record count like that. This should work in oracle 9i and above as they support at least part of the ASNI 99 SQL standards to include with. But oracle 9 does not allow recursive references in the with statement. But forthis that would work great.
  • Dan, this looks similar to the table variable that is in mssql 2000. you can stuff whatever you want into the variable, but it is only valid for the duration of the single query. Are you familiar with the table variable? Does it act the same way?

    I've used these to greatly increase query times by reducing complex joins over multiple large tables.
  • @shag:

    CTE's are much more powerful. Check out this example which allows you to do recursion to grab data in a traditional parent/child relationship (known as the adjacency list model.)

    http://blog.pengoworks.com/index.cfm/2007/11/30/Fi...
  • thanks, was looking for a better way to get the total number of results and I found this on google..it works great.
  • this saved me a couple hours time...thanks!!
    (select count(*) from pagination) as totalResults
  • @Matt:

    You can also do:

    with pagination as
    (
      -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
      select
        row_number() over (order by department, employee) as rowNo,
        , TotalRows=count(1) over()
        -- a list of the column you want to retrieve
        employeeId, employee, department
      from
        Employee
      where
        disabled = 0
    )

    This will do the same thing.
  • Michelle Kinsey Bruns's Gravatar
    Michelle Kinsey Bruns
    Most handy. And way quicker to integrate than most of the other zillion solutions out there, even with my need to dress it up as a sproc with dynamic SQL. Thanks.
  • Here is a simple way of getting pagination and total number of rows in one sql query
    http://darrylpye.wordpress.com/2009/10/23/sql-serv...
  • Genius!! Great post! Helped a great deal!
  • Thanks, I really appreciate it.

Comments for this entry have been disabled.