Turn: Brief introduction to the evolution of paging stored procedures based on SQL Server

Author: Zheng Zuo
Date: 2006-9-30

Paging of database data on the UI is a common problem. It is easy to find various "General Stored Procedures" on the Internet"CodeIn addition, 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 then apply it to the system.ProgramLayer 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 performance of SQL statement code writing Design
The first statement is simple and has good compatibility. It is rarely fully supported. The larger the data, the worse the performance.
The second type depends on the actual situation. Many sections support good performance, which is 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:
Empty description of column Name Data Type
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) +
'produd D, 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) +
'produd D, 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) +
'produd D, name from production. product order by productid ASC) T order by productid DESC '
end
/* run */
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
S Elect top (@ pagesize) * from (select top (@ topcount) productid, name from production. product order by productid ASC) T
order by productid DESC
end
the above stored procedure uses the new top (expression) function of 2005, this avoids String concatenation and simplifies 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)
 
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.

this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/heqi915/archive/2007/02/27/1516075.aspx