Paginated SqlServer Query with Changing Data
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.