Tim Chapman demonstrates how to design a stored procedure to page data automatically using the new ROW_NUMBER ranking function in SQL Server 2005.
Paging, the grouping together of records usually for display purposes, has never been easy to accomplish or very efficient in SQL Server. In the past, developers would have to design their stored procedures to accommodate paging using temp tables, or would have to return the entire result set back to the client and page the data set in that fashion. Neither method was very desirable from the point of view of the developer or the DBA.
With the advent of SQL Server 2005, ranking functions are introduced that will allow developers to write much cleaner and more efficient data paging procedures. These new ranking procedures provide functionality to number your result sets on the fly, rank your result sets, and order your results based upon a specified grouping of your result sets. In this article, I focus on the new ROW_NUMBER ranking function, which assigns a sequential number to each row of your result set based upon an ordering that you specify.
A paging example
I always like using examples to illustrate how to use new technology, so let's look at how to design a stored procedure to page data automatically using the new ROW_NUMBER ranking function.
First, I need to define the structure that I will use in my example. I define a SalesHistory table, which will contain sales records for products sold on our Web site. The table will contain common sale information such as the product sold, the sale date, and the sale price of the product. The script to create the table is below:
IF OBJECT_ID('SalesHistory','U') > 0
DROP TABLE SalesHistory
CREATE TABLE SalesHistory
SaleID INT IDENTITY(1,1),
Use the script in Listing A to load some sample sales data into the SalesHistory table.
Now that I have data to work with, let's look at the procedure that will be called from our company's Web page to return a paged data set for display. Listing B contains the script for the procedure. This procedure accepts two parameters: the PageSize (the number of records I want associated with a given page) and the TargetPage (the exact page for which I wish to retrieve records).
If you are new to using SQL Server 2005, the statement beginning in "WITH" will be unfamiliar. This statement begins a new feature in SQL Server 2005 called a common table expression (CTE), which is essentially a more efficient version of a temp table.
The meat of the paging is the TSQL statement inside of the CTE. In this select statement, I use the new ROW_NUMBER ranking function. (This is a pretty simple function to use. You need to provide a field list for the ROW_NUMBER function, which specifies how it should assign its number sequentially.) Then, I divide that row number by the @PageSize variable and take the ceiling of that value.
For example, if I have a result set with three records and decide I want a page size of two records, the first two records would be assigned to the first page because their rownumber would be less than or equal to the value 1. The third record would be assigned to the second page because its ceiling value would be less than or equal to 2 but greater than 1.
You can use the following script to call the stored procedure:
@PageSize = 3,
@TargetPage = 2
Here are the results I received from my sample data:
As you can see, the procedure has returned a page of data that contains three records, and I have returned the second sequential page of results from my data set.
Notes about my example
There will always be two ways to page data results: at the database or not at the database. Paging can be accomplished at the client, but when this occurs, the full data set must be returned and the page numbers determined once the full data set has been analyzed. In previous versions of SQL Server, paging could be accomplished at the database, but temporary tables would be required or table variables. While my example does incorporate a CTE to make use of paging, the example could have just as easily been accomplished without its use. This functionality makes the use of the ROW_NUMBER ranking function very powerful.
In a future article, I will provide an overview of the other three ranking functions, and show some of the neat functionality they provide.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.