Pagination in MSSQL 2005

Categories: Source Code, SQL

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.

Comments

James Allen's Gravatar 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.
James Allen's Gravatar Dan,
To add a GROUP BY clause, where would I add it - to the 'with pagination as' statement or the CTE table query?
Dan G. Switzer, II's Gravatar @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.
James Allen's Gravatar Lovely, thanks Dan.
Daniel D's Gravatar 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.
shag's Gravatar 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.
Dan G. Switzer, II's Gravatar @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...
KC's Gravatar thanks, was looking for a better way to get the total number of results and I found this on google..it works great.
Matt's Gravatar this saved me a couple hours time...thanks!!
(select count(*) from pagination) as totalResults
Dan G. Switzer, II's Gravatar @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 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.
darrylpye's Gravatar 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...
Murray's Gravatar Genius!! Great post! Helped a great deal!
coverboy's Gravatar Thanks, I really appreciate it.

Add Comment

Leave this field empty


If you subscribe, any new posts to this thread will be sent to your email address.