An analysis of evolution based on SQL Server paging stored procedures

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.