1

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:

  1. 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.
  2. Get the row number of the row that contains the last row id from the previous page, i.e. @LastOffset.
  3. 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.

6
0

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

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.