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 Web Controls 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 ten-thousands of records.

Hence the need for custom paging in a Web Control.

Custom paging various databases

Custom paging poses two 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 Web Control). 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 Web Control, but yet let it automatically show a page-row. With the DataGrid (ASP.NET 1.x), that was rather easy, but with the GridView (ASP.NET 2.0), 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 of my own. You'll find it in the previous article I wrote (Custom Paging and the GridView ASP.NET 2.0).

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.

Example

SELECT * FROM Products LIMIT 0, 10

The first argument denotes the offset from the first record found, and 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 conjunction with the keyword OVER), which can be used to retrieve a limited recordset.

I'm not going into great details 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.0, I strongly suggest you learn more about this approach. To help you, here are two 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 Access

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

However, there is one solution that consists only of one 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/ (the original entry is especially 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 Web Control. 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 the 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 third 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 SQL statement 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 first page = 10, the second = 20, the third = 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 its 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 two 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 short-circuited 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 to DESC, and all the DESC to 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 four 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.