I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is:
- Sort the table on create date with the most recent dates at the bottom (ASC), so that the changes to the table do not interfere with the overall result.
- Get the row number of the row that contains the last row id from the previous page, i.e. @LastOffset.
- Get the next set of rows as per the @Count parameter.
I have the following example:
-- Notes table sorted by Created date ASC NoteId BookId Username Created NoteText 1 2 abc 01/01/2015 Note 1 2 2 abc 01/01/2015 Note 2 3 2 abc 01/01/2015 Note 3 4 2 abc 01/02/2015 Note 4
I need to create a Stored Procedure that will return the following result given parameters: @LastOffset = 2, @Count = 2
-- Result set NoteId BookId Username Created NoteText 3 2 abc 01/01/2015 Note 3 4 2 abc 01/02/2015 Note 4
This is what I have so far:
DECLARE @StartOffsetRow int SET @StartOffsetRow = (SELECT RNUM FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, NoteId FROM Notes WHERE BookId = @BookId AND Username = @Username ) WROW WHERE WROW.NoteId = @StartOffset) SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, * FROM Notes WHERE BookId = @BookId AND Username = @Username ) WROW WHERE WROW.RNUM > @StartOffsetRow AND WROW.RNUM <= @StartOffsetRow + @Count
This setup seems to work, but contains repeat queries and a lot of select statements. Looking at the execution plan, there are 7 queries. I was wondering if there is a different approach to this problem with fewer calls. I am using SQL 2008 R2 server.
Quick and dirty example for smaller data sets. For larger data sets I definitely recommend checking out the link Aaron Bertrand provided: http://www.sqlservercentral.com/articles/paging/69892/
declare @pagewidth int, @pagenumber int
select * from ( select *, row_number() over (order by PAGINGCOLUMN/S) as cnt from datatable )a where cnt > (@pagenumber - 1) * @pagewidth and cnt <= @pagenumber * @pagewidth