CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Royi Namir
Royi Namir

Reputation: 148644

Sql server - dealing with new data?

A rock band has currently 100 songs : 

select count(songName) from bands where name='Beatles'

result : 100.

I display those songs in my app via paging ( each time - 10 results)

How do I get the relevant page and its rows ?

like this : (SP)

declare @pageItems int=10
declare @pagenum int=1

select * from (
    SELECT  [id] , row_number() over ( order by songName) as n
    FROM Bands where name='Beatles'
) a 

where a.n > (@pagenum-1)*@pageItems  and a.n<=  (@pagenum)*@pageItems

But there is a problem.

Suppose a user is at page 3.

And the beatles publish a new song named : "aaa"

So now , there will be a mismatch because there is a new row which is inserted at the top ( and pushes all rows below).

I dont want to get all the rows into my app.

What is the correct way / (feature?) to get the first criteria results ?

I could use a temp table but it will be only for the current connection. ( and each time a user press "next" at paging - it's a new session).

Upvotes: 0

Views: 35

Answers (1)

sisdog
sisdog

Reputation: 2719

This is a common problem in paging, whether or not to adjust to data that is moving. Usually your users won't notice, or care, that the total number of records changes during pagination or if a record that was at the bottom of page 2 suddenly is at the top of page 3 because a new record was added. But, if you really need the consistency, then if the dataset is small enough you can cache the entire list of PK's if a temp table or in your apps memory and then just join to that when fetching.

-> What is the best way to paginate results in SQL Server

Upvotes: 1

Related Questions