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.