cetin (1/6/2009)
I think the code in that article (the one that uses row_number) has one problem. What if:User A retrieves first page (rows 1-25 in name order)
User B deletes one or more rows that user A retrieved
User A requests next page (she thinks she is getting 26-50 in name order, and she does, but now some names are shifted to page 1 due to deletion and she missed them)
The other code here that uses persisted identity column is better IMHO.
Cetin,
You bring up a very valid point, but you introduce a new problem... Sequence gaps. Perhaps the user will be even more concerned why row 25 does not exist on any page, if deleted. Using the posted method allows the user to get a realistic look at the data, as it exists in the database.
Is using Row_Number pagination always the best solution... no. The best solution depends on your environment and your data. The article provides easy to setup and performant means to accomplish pagination in SQL 2005.
Edit: My response was to using a table identity column. I just reviewed the posted code above and the would work the same way as the row number solution posted. The difference here is you CANNOT gaurentee the sequencing of the data inserted with the INSERT INTO statement. This in itself can throw off your sequencing.