Paginated SqlServer Query with Changing Data 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 ROW_NUMBER()".

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.
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.


I'm assuming that your clustered index is the same thing as your primary key. This is possible to do in SQL Server but the syntax isn't the most intuitive. Your code will look more complicated as the number of primary key columns increases. The basic technique that you want is described here.

I'm going to use the following table with a two column primary key as an example:

CREATE TABLE your_table (
    PK_COL1 INT NOT NULL,
    PK_COL2 INT NOT NULL,
    OTHER_COLUMN VARCHAR(100),
    PRIMARY KEY (PK_COL1, PK_COL2)
);


Consider the following query:

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 &gt; @id2) OR t.PK_COL1 &gt; @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;


That will very efficiently give you the next 20 rows following the last primary key that you've already processed in the app. All that you need to do is get 20 rows, keep track of the latest primary key that you processed in the app, then use the latest primary key to set @id1 and @id2 to get the next 20 rows. Since you're using the primary key as an anchor it shouldn't matter if a new row is inserted in the middle of data that you've already processed.

We can throw a little data into the table for a demo:

INSERT INTO your_table WITH (TABLOCK)
SELECT
t.RN / 16
, 1 + t.RN % 16
, REPLICATE('Z', 100)
FROM
(
    SELECT TOP (10000) -1 +
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN  master..spt_values t2
) t
OPTION (MAXDOP 1);


Let's say that we've processed up to the row with PK_COL1 of 10 and PK_COL2 of 3:

DECLARE @id1 INT = 10;
DECLARE @id2 INT = 3;

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 &gt; @id2) OR t.PK_COL1 &gt; @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;


I get a very fast clustered index seek:



STATISTICS IO results: 


  Table 'your_table'. Scan count 2, logical reads 4


The result set is as follows:

╔═════════╦═════════╗
║ PK_COL1 ║ PK_COL2 ║
╠═════════╬═════════╣
║      10 ║       4 ║
║      10 ║       5 ║
║      10 ║       6 ║
║      10 ║       7 ║
║      10 ║       8 ║
║      10 ║       9 ║
║      10 ║      10 ║
║      10 ║      11 ║
║      10 ║      12 ║
║      10 ║      13 ║
║      10 ║      14 ║
║      10 ║      15 ║
║      10 ║      16 ║
║      11 ║       1 ║
║      11 ║       2 ║
║      11 ║       3 ║
║      11 ║       4 ║
║      11 ║       5 ║
║      11 ║       6 ║
║      11 ║       7 ║
╚═════════╩═════════╝


For the next batch, you would use @id1 INT = 11 and @id2 = 7.