Using RowCount for sorting when paging large result sets

Introduction

This article adds further investigation to paging of large resultsets in ASP.NET in: Paging of Large Resultsets in ASP.NET.

Have you ever performed paging using a mechanism which is described in Microsoft document?

SELECT * FROM PROUCT WHERE ID>@lastProductID

However, there is one prerequisite, the product ID must be an identity column in SQL Server and the statement must not contain order by clause according to this equation. This poses a limitation. How about if we want to do sorting? This article will simply illustrate a method to allow the sorting mechanism to work.

Using the code

Assume that we have a product with two columns, one is ID and the other one is price, where ID is a unique identity column, and price is a non-unique and non-nullable column, and we want to sort by price. The SQL is:

SELECT * FROM Product ORDER BY Price, ID will give

    ----------------------------------------
   |       ID         |        Price       |
    ----------------------------------------
           3                     2
           4                     2
           5                     2
           1                     3
           2                     3

Back to the basics, if we want to display the product without sorting, suppose you have two items in each ASPX page, the SQL of the first page is:

SET ROWCOUNT 2 SELECT * FROM PRODUCT WHERER ID>0

And the SQL for the second page (assuming the last product ID in the first page is 2) is:

SET ROWCOUNT 2 SELECT * FROM PRODUCT WHERER ID>2

That is easy, but for sorting cause, if we want to sort by price, the equation is:

SET ROWCOUNT 2
SELECT * FROM Product WHERE ID>@lastProductID and Price = @lastSortByID 
  UNION ALL
SELECT * FROM Product WHERE Price > @lastSortByID ORDER BY Price, ID

Or, if you would like, you can use a OR instead of UNION ALL in the SQL.

SET ROWCOUNT 2
SELECT * FROM Product WHERE (ID>@lastProductID and Price = @lastSortByID) 
        OR (Price > @lastSortByID) ORDER BY Price, ID

For the first page, by applying the equation (assuming -1 is the impossible value for price column):

SET ROWCOUNT 2
SELECT * FROM Product WHERE ID>0 and Price = -1 
  UNION ALL
SELECT * FROM Product WHERE Price > -1 ORDER BY Price, ID

According to the table, there is no records in the first union join and there are two records in the second union join. The last item in the first page has an ID of 4 and price of 2 (the second record in the above table), so we put these two values in the SQL statement in the next page, by applying the equation for the second page.

SET ROWCOUNT 2
SELECT * FROM Product WHERE ID>4 and Price = 2 
    UNION ALL
SELECT * FROM Product WHERE Price > 2 ORDER BY Price, ID

The first union join will give 1 record and the second will give 1 record as well. The first record will give price of 2 and ID of 5 (the third record in the above table), and the second will give price of 3 and ID of 1 (the forth record in the above table).

That's it. This mechanism works with limitation. The sort by column is not nullable. This is explainable. The null columns cannot be compared using the comparison operator. You cannot write the SQL like "WHERE Price = NULL". If you are dealing with nullable columns, you must change the SQL to a more complicated form which I will not explain here. I also reckon that any searchable columns should not be nullable, otherwise, you ask troubles yourself.

This mechanism is complicated in the client side, as you have to keep track of the view state of the last sort by column value and the last unique column value. I spent quite a lot of time writing the client side code. One special situation that I want to illustrate is when the user is browsing the last item in the last page, and the last page contains only one item. Suddenly, the administrator removes that item, and the user refreshes that page again, then an error is generated. These kind of errors have to be handled as well.

Reference:

• Paging of Large Resultsets in ASP.NET.

History

I am not a very good developer, and if I made something wrong in the article, please correct.

Thanks.