The traditional way to do server-side pagination of queries with SqlServer (earlier than 2012) has been to use
ROW_NUMBER(), as somewhat covered here and in other places.
Let's say that I'm building an "infinite scroll" UI component in a web app, and using such a paginated query on the server side to fetch blocks of results which are then rendered. When the user scrolls down, the next page of results are fetched using the
ROW_NUMBER() of the last record from the previous request as the starting point for a new request.
This works very well, but I'm wondering what to do when the underlying data changes. If my results are ordered by time, and a new record is entered, that new record would be at the "top" of the list. The
ROW_NUMBER() of the results would then all shift by one, breaking the continuity of the infinite scroll, thereby producing a duplicate returned record. Deleting a record results in the opposite problem of a "disappearing" record because the client already "fetched that
Is there a way to overcome this in the query? I know I could suck out the entire result set and cache it in memory in my App Server or something, creating a sort of "data freeze" at least for that session, but I'd prefer not to. Given that each row in the result set has a unique PK aside from the
ROW_NUMBER() value, it seems like it might be possible to ask "give me the next 20 rows starting with this PK" to avoid shifting
ROW_NUMBER() values, but I can't see a way of expressing this.