How to implement SQL Server 2005 Fast Web Paging

This article focuses on SQL Server 2005 's newly added row-number sorting function, which classifies the result dataset according to the classification criteria you specify, and assigns consecutive pages to the dataset for your reference!

Paging is the display of grouped datasets according to some rule, but in SQL Server paging is not as easy to implement. In the past, developers often needed to write their own programs, use temporary tables to implement paging, or return all data result sets to the client for paging operations on the client. Neither approach is satisfactory from the point of view of the developer or DBA.

With the release of SQL Server, some of these sort functions make it simpler and more efficient for developers to write data paging programs. These new sorting functions provide the number of statistical data sets, classify datasets, and sort data sets by some standard. In this article, I will highlight the newly added row-number sort function, which classifies the result dataset according to the classification criteria you specify, and assigns consecutive pages to the dataset.

An instance of a paging

I always like to show you how to use the new technology with examples, so let's look at how to design a stored program and use the Row_number function to automate pagination of the data.

First, you need to define some data structures. We define a saleshistory form that contains data about the sales records of products we sell online. Includes some common sales information, such as the product sold, the date of sale, the price of the product sold, and so on. The following script is to create a table like this:

CODE:

IF OBJECT_ID('SalesHistory','U') > 0
DROP TABLE SalesHistory
CREATE TABLE SalesHistory
(　　　
SaleID INT IDENTITY(1,1),　　　
Product VARCHAR(30),　　　
SaleDate SMALLDATETIME,　　　
SalePrice MONEY
)

The script in run list A adds some example data to the Saleshistory table created above.