1

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.

  • If the underlying data changes, your query must be run again. The user experience would be negatively affected in that case, so you would have to cache the original query and keep using that dataset. – Randolph West Jul 2 '17 at 19:16
  • no you don't have to do anything.default behavior is ok.when user scroll down then according to page-index you fetch data and populate only part of page.when user scroll up then you don't do any thing.however when user refresh page or click something then you can refresh those data.you can take example of another site. – KumarHarsh Jul 3 '17 at 5:34
  • Just to confirm: do you have a business requirement that showing the same record twice (or possibly "missing" a record) is a huge problem? most sites using pagination simply go with whatever the current "records 61-75) are, without worrying about what might be shown in that range that was also shown in 46-60, or what might not be seen because what was record 61 last time is now record 60. This site works this way. If your business rules dictate that everything must be seen through this process, you'd probably need to select the row # and IDs into a table (as Randolph West suggest). – RDFozz Jul 3 '17 at 15:38
2

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 > @id2) OR t.PK_COL1 > @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 > @id2) OR t.PK_COL1 > @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;

I get a very fast clustered index seek:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.