server| Stored Procedures | paging
A brief discussion on the evolution of SQL SERVER paging stored procedures
Author: Zhengzo
Date: 2006-9-30
For database data in the UI interface pagination is a cliché question, the Internet is easy to find a variety of "common stored Procedures" code, and some also customized query conditions, it looks very convenient to use. I intend to use this article to briefly discuss the paging stored procedure based on SQL Server 2000, while talking about the evolution of the paging stored procedure under SQL Server 2005.
In the data paging based on the UI display, there are two main kinds of data extraction methods. The first is to extract all the data from the database and then page the data in the System application layer, displaying the current page data. The second way to page out is to display a page of data that needs to be displayed from the database on the UI interface. The following is the author of the two ways to achieve the advantages and disadvantages of comparison, for application writing, the author. NET technology platform for example.
| Category |
SQL statement |
Code writing |
Design time |
Performance |
| First Kind |
Simple statement, good compatibility |
Rarely |
Full support |
The bigger the data, the worse the performance. |
| Second Kind |
Look at the specifics. |
More |
Partial support |
Good, related to SQL statements |
For the first case this article does not intend to give examples, the second implementation of the author only two times top method to discuss.
Before you write a specific SQL statement, define the following data tables.
The data table name is: Production.Product. Production is an improved data-table schema for SQL SERVER 2005 that does not affect the example.
Fields included:
| Column Name |
Data type |
Allow null |
Description |
| ProductID |
Int |
|
Product ID,PK. |
| Name |
Nvarchar (50) |
|
Product name. |
It is not difficult to find that the table structure above comes from the Production.Product table in the SQL SERVER 2005 sample database AdventureWorks and takes only two of the fields. Page-related elements:
pageindex– page index count, Count 0 is the first page.
pagesize– each page display size.
Total number of recordcount– records.
pagecount– pages.
For the latter two parameters, the author provides the output parameters in the stored procedure.
1
. Top paging in SQL SERVER 2000
CREATE PROCEDURE [Zhzuo_getitemspage]
@PageIndex INT,/* @PageIndex from Count, 0 for first page * *
@PageSize INT,/* Page Size * *
@RecordCount INT out,/* Total number of records * *
@PageCount INT Out/* page */as/* get the number of records * *
SELECT @RecordCount = COUNT (*) from Production.Product
/* Calculate page Data * *
SET @PageCount = CEILING (@RecordCount * 1.0/@PageSize)
/*top Record number * *
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-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-ProductID ASC ' T order by ProductID DESC '
End
end/* Execution * *
EXEC (@SQLSTR)
The above stored procedures determine the number of pages, if the first or last page, for special treatment. Other cases use the top Flip 2 times. Where the sorting condition is ProductID reverse. Finally executes the SQL string puzzle through execute.
2
.
SQL SERVER
in the
Top
Paging
CREATE PROCEDURE [dbo]. [Zhzuo_getitemspage2005top]
@PageIndex INT,
@PageSize INT,
@RecordCount INT out,
@PageCount INT out
AS/* Get the number of records * *
SELECT @RecordCount = COUNT (*) from Production.Product
/* Calculate page Data * *
SET @PageCount = CEILING (@RecordCount * 1.0/@PageSize)
/*top Record number * *
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 to Production.Product order by ProductID DESC
End
ELSE
BEGIN
IF @PageIndex = @PageCount-1
BEGIN
SELECT * FROM (select Top (@TOPCOUNT) Productid,name to 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 of the stored procedure is the use of 2005 of the Top (expression) of the new function, to avoid string strings, so that the structure of the query language become concise. Implemented for the same function.
3
.
SQL SERVER
new pagination in
CREATE PROCEDURE [dbo]. [zhzuo_getitemspage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT out,
@PageCount INT out
AS/* Get 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 2005 of new functionality, and the implementation of the paging stored procedure functionality is simpler and easier to understand. Note that ProductID here is the primary key, sorted by ProductID to generate Row_number, and row_number to determine the number of pages.
With a comparison of three paging stored procedures, the visual SQL SERVER TSQL language supports paging functionality with a lot of progress. Make paging implementations tend to be simplistic.
