Paginating a SQL query result set

Question

Post reply

Paginating a SQL query result set

gjryan59

Mr or Mrs. 500

Points: 536
More actions
May 6, 2014 at 7:48 pm

#307139

Comments posted to this topic are about the item Paginating a SQL query result set

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

Seems kind of elaborate to replace:

select top 10 column1, column2, column3 from table where column1 > 99 order by column1

or

select top 10 column1, column2, column3 from table where column1 > (99 + (page * 10) )order by column1

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

OFFSET/FETCH is the prefered method for pagination, I believe

trimjib SSC Veteran Points: 234 More actions · Answer 3

As Ryan pointed out the real problem is with the query dataset not being utilized for subsequent page calls. The cost is in preparing the dataset, not in grabbing a few rows from it.

Does Offset/Fetch notify SQL to optimize that aspect of pagination?

gjryan59 Mr or Mrs. 500 Points: 536 More actions · Answer 4

The reason it is written the way it is, is that the underling data may change between page changes on the mobile devices that this was written for. Hence not cashing the dataset and just paging through it.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

trimjib (11/2/2015)
As Ryan pointed out the real problem is with the query dataset not being utilized for subsequent page calls. The cost is in preparing the dataset, not in grabbing a few rows from it.
Does Offset/Fetch notify SQL to optimize that aspect of pagination?

The previous results would be cached.