Pagination In SQL Server 2005
(
May 26 2007 - 05:43:25 PM by
Timothy Khouri) - [
print article]
Pagination In SQL
If you've used other flavors of SQL (such as MySQL or PostgreSQL) and now you have switched over to TSQL (Microsoft SQL Server) you may have noticed that there is no native way of doing pagination. In some SQL versions, pagination was as easy as "SELECT * FROM 'myTable' LIMIT 0, 10" and that would pull out the first 10 records from your table. SQL Server 2005 introduced a beautiful new world of 'ranking functions', one of which we can use to easily perform pagination directly in SQL.
While these other flavors of SQL implimented pagination only for the sake of pagination - meaning the absolutely only reason for the functions is to limit results sets into 'pages' of data - SQL Server 2005's ranking functions provide a lot of power and information. However, for this article, we are only going to demonstrate how to use these abilities for the sake of pagination.
RowNumber In SQL
First of all, we'll need to get the "row number" from a result set. This is the basis of pagination because you need to know what row you are on to begin demanding only a subset from SQL. First we'll do just a basic query and look at the results:
SELECT
*
FROM
dbo.Customers
ORDER BY
LastName, FirstName
This query above will display the following results:
Now we'll make a slight modification to add a "RowNumber" field.
SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
*
FROM
dbo.Customers
This syntax may look a little odd, but it's not that difficult. First of all, the ROW_NUMBER() function (just like all other 'ranking functions' in SQL SERVER 2005) needs an OVER clause. Notice that we moved the ORDER BY clause inside the OVER clause. Basically what this is doing is telling SQL how we want the ROW_NUMBER() function to number each record. So we are telling SQL to number the records 1, 2, 3, 4 in the order of LastName then FirstName. Here are the results:
So now that we have a "RowNumber" field, we can add a simple WHERE clause to get only a limited number of records. We will have to wrap query in another SELECT statment so that we can use all of our dynamic columns (such as our RowNumber column) by their names that we assigned them. Also, I'm going to use the TOP clause to limit the query to only return 2 records at a time. Lets see the query, and the results:
SELECT TOP 2 * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
*
FROM
dbo.Customers) _myResults
WHERE
RowNumber > 2
Notice I 'aliased' the first query and called it "_myResults". This basically treats that inner query as a VIEW so that I could pull out that first column by it's name, RowNumber. Here are the results:
Conclusion
Though it may seem like you have to do a lot more work in order to get pagination in SQL Server 2005, the pay-off is that not only does the code make sense, but you gain a lot more power with the ranking functions TSQL. Try playing around with the parameters to see how they affect your queries and what you can do with them.