Analysis on the evolution of paging stored procedures based on SQL Server

Paging of database data on the UI is a common issue. It is easy to find various "General stored procedure" codes on the Internet, and some custom query conditions make it easy to use. I plan to use this article to briefly discuss the paging stored procedures based on SQL Server 2000 and the evolution of paging stored procedures under SQL Server 2005.

There are two common data extraction methods for paging UI-based data. The first is to extract all data from the database and paging the data at the system application layer to display the current page number. The second paging mode is to retrieve a page of data to be displayed from the database and display it on the UI.
The following is a comparison of the advantages and disadvantages of the two implementation methods. For application programming, the author takes the. NET technology platform as an example.

 

Category	SQL statement	Code Writing	Design Time	Performance
First	Simple statement, good compatibility	Very few	Fully supported	The larger the data, the worse the performance.
Second	View Details	Many	Partially supported	Good, related to SQL statements

For the first case, this article does not intend to give an example. For the second implementation method, I will only discuss it in the top mode twice.
Before writing a specific SQL statement, define the following data table.
The data table name is production. Product. Production is the improved data table architecture in SQL Server 2005, which does not affect the example.
The fields include:

Column name	Data Type	Null allowed	Description
Productid	Int		Product ID, PK.
Name	Nvarchar (50)		Product Name.

It is not difficult to find that the above table structure comes from the production. Product table of the SQL Server 2005 sample database adventureworks, and only two fields are taken. Paging related elements:
Pageindex-page index count. 0 indicates the first page.
Pagesize-the size of each page.
Recordcount-Total number of records.
Pagecount-page number.

For the last two parameters, I provide them as output parameters in the stored procedure.
1. Top pages in SQL Server 2000

Create procedure [zhzuo_getitemspage]
@ Pageindex int,/* @ pageindex from Count, 0 is the first page */
@ Pagesize int,/* page size */
@ Recordcount int out,/* Total number of records */
@ Pagecount int out/* page number */
As
/* Obtain the number of records */
Select @ recordcount = count (*) from production. Product
/* Calculate page data */
Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize)
/* Number of top records */
Declare @ topcount int
Set @ topcount = @ recordcount-@ pagesize * @ pageindex
Declare @ sqlstr nvarchar (1000)
If @ pageindex = 0 or @ pagecount <= 1
Begin
Set @ sqlstr = n' select top' + STR (@ pagesize) +
'Productid, name from production. product order by productid DESC'
End
Else
Begin
If @ pageindex = @ pagecount-1
Begin
Set @ sqlstr = n' select * from (select top '+
STR (@ topcount) + 'productid,
Name from production. product order by productid ASC)
T order by productid DESC'
End
Else
Begin
Set @ sqlstr = n' select top' + STR (@ pagesize) +
'* From (select top' + STR (@ topcount) + 'productid,
Name from production. product order by productid ASC) T order by productid DESC'
End
End
/* Execute */
Exec (@ sqlstr)

Create procedure [zhzuo_getitemspage] @ Pageindex int,/* @ pageindex from Count, 0 is the first page */ @ Pagesize int,/* page size */ @ Recordcount int out,/* Total number of records */ @ Pagecount int out/* page number */ As /* Obtain the number of records */ Select @ recordcount = count (*) from production. Product /* Calculate page data */ Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) /* Number of top records */ Declare @ topcount int Set @ topcount = @ recordcount-@ pagesize * @ pageindex Declare @ sqlstr nvarchar (1000) If @ pageindex = 0 or @ pagecount <= 1 Begin Set @ sqlstr = n' select top' + STR (@ pagesize) + 'Productid, name from production. product order by productid DESC' End Else Begin If @ pageindex = @ pagecount-1 Begin Set @ sqlstr = n' select * from (select top '+ STR (@ topcount) + 'productid, Name from production. product order by productid ASC) T order by productid DESC' End Else Begin Set @ sqlstr = n' select top' + STR (@ pagesize) + '* From (select top' + STR (@ topcount) + 'productid, Name from production. product order by productid ASC) T order by productid DESC' End End /* Execute */ Exec (@ sqlstr)

The above Stored Procedure determines the number of pages. If it is the first or last page, special processing is performed. In other cases, use top flip twice. The sorting condition is productid in reverse order. Finally, execute the SQL string spelling string through execute.

2. Top pages in SQL Server 2005

Create procedure [DBO]. [zhzuo_getitemspage2005top]
@ Pageindex int,
@ Pagesize int,
@ Recordcount int out,
@ Pagecount int out
As
/* Obtain the number of records */
Select @ recordcount = count (*) from production. Product
/* Calculate page data */
Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize)
/* Number of top records */
Declare @ topcount int
Set @ topcount = @ recordcount-@ pagesize * @ pageindex
/* Based on SQL Server 2005 */
If @ pageindex = 0 or @ pagecount <= 1
Begin
Select top (@ pagesize) productid,
Name from production. product order by productid DESC
End
Else
Begin
If @ pageindex = @ pagecount-1
Begin
Select * from (select top (@ topcount) productid,
Name from production. product order by productid ASC) T
Order by productid DESC
End
Else
Begin
Select top (@ pagesize) * from (select top (@ topcount) productid,
Name from production. product order by productid ASC) T
Order by productid DESC
End
End

Create procedure [DBO]. [zhzuo_getitemspage2005top] @ Pageindex int, @ Pagesize int, @ Recordcount int out, @ Pagecount int out As /* Obtain the number of records */ Select @ recordcount = count (*) from production. Product /* Calculate page data */ Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) /* Number of top records */ Declare @ topcount int Set @ topcount = @ recordcount-@ pagesize * @ pageindex /* Based on SQL Server 2005 */ If @ pageindex = 0 or @ pagecount <= 1 Begin Select top (@ pagesize) productid, Name from production. product order by productid DESC End Else Begin If @ pageindex = @ pagecount-1 Begin Select * from (select top (@ topcount) productid, Name from production. product order by productid ASC) T Order by productid DESC End Else Begin Select top (@ pagesize) * from (select top (@ topcount) productid, Name from production. product order by productid ASC) T Order by productid DESC End End

The above stored procedure uses the new top (expression) function of 2005 to avoid String concatenation and simplify the Structured Query Language. The same function is implemented.

3. New pages in SQL Server 2005

Create procedure [DBO]. [zhzuo_getitemspage2005]
@ Pageindex int,
@ Pagesize int,
@ Recordcount int out,
@ Pagecount int out
As
/* Obtain the number of records */
Select @ recordcount = count (*) from production. Product
/* Calculate page data */
Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize)
/* Based on SQL Server 2005 */
Select serialnumber, productid, name from
(Select productid, name,

ROW_NUMBER() OVER (ORDER BY ProductID DESC) 

AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and 

T.SerialNumber <= ((@PageIndex+1) * @PageSize) 

Create procedure [DBO]. [zhzuo_getitemspage2005] @ Pageindex int, @ Pagesize int, @ Recordcount int out, @ Pagecount int out As /* Obtain the number of records */ Select @ recordcount = count (*) from production. Product /* Calculate page data */ Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) /* Based on SQL Server 2005 */ Select serialnumber, productid, name from (Select productid, name, ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)

The third stored procedure uses the new functions under 2005, which makes the paging stored procedure more simple and easy to understand. Note that productid is the primary key. row_number is generated based on the productid order. row_number is used to determine the specific page number.

By comparing the three paging stored procedures, we can see that the tsql language of SQL Server has greatly improved its support for paging functions. The paging implementation tends to be simplified.