Posted by Erland Sommarskog on 10/24/36 11:35

Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes:
> Erland Sommarskog wrote:
>>
>> An alternative is to use a permanent table, that would have some
>> session key and an IDENTITY column (in SQL 2000). When the user makes
>> his first search, you get all data into that table. Then as he pages
>> on, you retrieve the rows from this table. This means you don't have to
>> redo the query for subsequent pages, but can get it from the table.
>> This is likely to give
>
> Depending on the actual practical needs, yet another variation of this
> technique is to save only the primary keys into the permanent table.
> Requires less disk space and displays changes made after the PK set was
> materialized.

Good point. There is a potential problem, though, if rows can be deleted.
(But this could be indicated when returning the data.)

There is also a risk for confusion, if the user selects data to be
sorted by something which is not in the key, for instance price, and the
price is updated while the user is paging.

What this really boils down to is that to implement paging properly, you
need to understand the business domain.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]