[转]各种分页 in SQL Server 2005, SQL Server 2000, MS Access and MySQL

转自: http://www.codeproject.com/useritems/paging_various_databases.asp
作者: Erwin@ODS
标题: Paging records in SQL Server 2005, SQL Server 2000, MS Access and MySQL

Introduction

As a Web developer, you should know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView is NOT recommended, simply because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases (the kind, say, a Microsoft programmer would use to develop a new feature), but in the real, ugly world outside, databases tend to have tables with several tenthousands of records.

Hence the need for custom paging in a WebControl.

Custom paging various databases

Custom paging poses 2 problems :

1. How to get a page of records from a database (I use "a page of records" or "paged records" to indicate a limited recordset, consisting of only those records that are needed to be displayed by the ASP.NET Webcontrol). Getting these records however depends on the data-server, so you need to know the various methods. This is what this article is about.
2. The second problem is how to display only the limited recordset in the webcontrol, but yet let it automatically show a pagerrow. With the DataGrid (ASP.NET 1.x) that was rather easy, but with the GridView (ASP.NET 2) it's a whole other story, especially when the binding has to be done not at design time, i.e. declaratively, but at run time (programmatically). I did not find anything useful on the Web to solve this problem, so I came up with one my own. You'll find it in the previous article I wrote (Custom_Paging_GridView.asp).

However, as far as the first problem is concerned (getting paged records from a database), there are lots of solutions on the web. Most of this article will just point to those solutions, and give some examples (using the Northwind sample database).

Let's start with the simplest solution, provided by MySQL.

MYSQL

MySQL comes with a LIMIT clause to be used in the SELECT statement.

Ex.

SELECT * FROM Products LIMIT 0, 10

The first argument denotes the offset from the 1 record found, the second argument indicates how many records should be returned. So the previous example limits the recordset to the first 10 records in the table Products.

To find the next batch, you use "SELECT * FROM Products LIMIT 10, 10", and then LIMIT 20, 10 etc..

(see http://dev.mysql.com/doc/refman/5.0/en/select.html)

The LIMIT clause is available in MySQL versions 4 and 5, but I don't know if previous versions support it.

SQL Server 2005

Long in need of something like LIMIT, Microsoft provided SQL Server 2005 with a new function ROWNUMBER() (used in conjuction with keyword OVER), which can be used to retrieve a limited recordset.

I'm not going into great detail of this solution, because of two things :

• it is not nearly as simple as the LIMIT clause in MySQL (Microsoft should REALLY get working on something like it !)
• it's SQL Server 2005 only, so it does not work for previous versions

However, if you want to use the GridView and the ObjectDataSourceControl declaratively in ASP.NET 2, I strongly suggest you learn more about this approach. To help you, here are 2 links :

http://www.asp.net/learn/dataaccess/tutorial25cs.aspx?tabid=63

http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx

SQL Server 2005, SQL Server 2000, SQL Server 7 and MS Acces

There are several solutions for paging records if you work with the database-servers listed above. But many of them involve working with temparory tables or cursors, which of course take a heavy toll performance-wise.

However, there is one solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! But on the other hand, it's rather complicated.

First, let me give you the link where I found it :

http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/

(especially the original entry is interesting and also reply # 39)

Here is the template of the SQL statement you should use :

SELECT t.fields FROM (
     SELECT TOP x id_field, sort_field FROM (
          SELECT TOP y id_field, sort_field        
          FROM table
          WHERE conditions
          ORDER BY sort_field ASC, id_field ASC) AS foo
     ORDER BY sort_field DESC, id_field DESC) AS bar
INNER JOIN table AS t ON bar.id_field = t.id_field
ORDER BY bar.sort_field ASC, bar.id_field ASC

Legend :

SQL SERVER KEYWORDS : the words in uppercase should not be changed

t = alias of table/view

fields = name of the field(s) you want to display in the webcontrol. They should include the primary key field(s) of the table/view (whether it's displayed or not).

x = rows per page (number of rows you want to have returned)

id_field = primary key field(s) of table/view

sort_field = fields on which you want to sort the table (optional). Notice however that the sort fields always include the primary key field(s) ! This is to avoid ambiguous results.

y = the maximum number of records required. This requires a little calculation : y = pagenumber * records_per_page. So if you want to display the 3rd page, and there are 10 records per page, then y = 3 x 10 = 30. (However, the SQL statement will only return 10 records, but y has to be the maximum number of records required).

table = name of the table/view from which to get the recordset

conditions = usual WHERE-clauses go here

foo = just a name, you can use it or choose another one

bar = just a name, idem

Example

This will give you the first 10 products from the table Products of which the UnitsInStock is less than 200, ordered by ProductName. Fields you want to display in the GridView are ProductID, ProductName, UnitPrice and UnitsInStock :

SELECT p.ProductID, p.ProductName, p.UnitPrice, p.UnitsInStock FROM
     (SELECT TOP 10 ProductID, ProductName FROM 
          (SELECT TOP 10 ProductID, ProductName
          FROM Products
          WHERE UnitsInStock < 200
          ORDER BY ProductName ASC, ProductID ASC) AS foo
     ORDER BY ProductName DESC, ProductID DESC) as bar
     INNER JOIN Products p ON bar.ProductID = p.ProductID
ORDER BY bar.ProductName ASC, bar.ProductID ASC

The following will give the second page. Note that it is just the 'y' value that's changed !

SELECT p.ProductID, p.ProductName, p.UnitPrice, p.UnitsInStock FROM
     (SELECT TOP 10 ProductID, ProductName FROM 
          (SELECT TOP 20 ProductID, ProductName
          FROM Products
          WHERE UnitsInStock < 200
          ORDER BY ProductName ASC, ProductID ASC) AS foo
     ORDER BY ProductName DESC, ProductID DESC) as bar
     INNER JOIN Products p ON bar.ProductID = p.ProductID
ORDER BY bar.ProductName ASC, bar.ProductID ASC

How does this work ?

You should read the sqlstatement from the inside out :

1. The innermost SELECT statement

1. The innermost SELECT statement just gives you the primary key fields (+ optionally the sort fields) of the relevant records (so, the WHERE clause is placed in the innermost SELECT statement)
2. These records are sorted in ASCending order (see remark 2 if you want it in DESCending order)
3. Also the number of rows are limited to the maximum rows required. Assuming that your page contains 10 rows, the number of records required for the 1st page = 10, the 2nd = 20, the 3rd = 30, etc. This means that if you have a table with 10.000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10.000 records. This of course has it's ramifications on performance, but since it's only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal. Also, the WHERE clause will limit the number of the base recordset.
4. This recordset is named 'foo'.

2. The middle SELECT statement

1. From the 'foo' recordset, the middle SELECT statement only selects the records to display. The number of rows to display is set by the TOP x clause (where x = the number of rows to display). It will be the last 'x' records because of the reverse sorting order.
2. This gives you a recordset, named 'bar'.
3. This recordset also consists of only 2 kind of fields : the primary key fields and the sort fields.

3. The outer SELECT statement

1. In the outer SELECT statement, the primary key field(s) of 'bar' is shortcircuited with the primary key field(s) of the source table/view (with the INNER JOIN clause) so now it is possible to retrieve other fields (like UnitPrice and UnitsInStock). Again, this recordset has to be sorted in ascending order.

Conclusion

The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets.

This means that there should be at least ONE field to be sorted. If there isn't any, sort on the primary key field(s) !

Remarks

1. When sorting, put the primary key fields AFTER the sorting fields
2. If you want to have the results in DESCending order, then you have to change all the ASC keywords in DESC, and all the DESC in ASC, and there you have it !
3. A problem arises when you come to the end of the table. Say you have 84 records in your table, and you want to have the last page, there should only be 4 records returned. Unlike the LIMIT statement in MySQL, you have to do the calculation yourself. This means that you need to keep track if the last page is requested, and if so, you need to calculate the number of records returned. This then should be the value of x in the SQL statement.