Sql-server – How to get paged resultset from a table

optimizationquery-performancesql serversql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – How to get the first or last row in ordered result set, depending by column value

Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID 
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID 
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues 
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME

SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions

Sql-server – Get a specific row from a TSQL common table expression

There are a few ways to do this. If there is a 1-2-1 relationship between the parents and widgets ( ie a parent only has one widget and vice versa ) you can just add a column in the CTE to count how many rows have passed through. See the xlevel example below.

If that doesn't work for you ( and I would probably expect a parent to have multiple widgets ) then you could just throw all the results into a temp table using the IDENTITY function, then grab the third row. Of course you need some kind of meaningful order for this to work, but see the #tmp table version below.

You could also use ROW_NUMBER(), available in SQL Server 2008 to add an arbitrary row number to the resultset. Again you need some kind of order to give it meaning. See the ROW_NUMBER example below:

;WITH GroupList(xlevel, WgID,WgParentWgID) AS (
SELECT 1 xlevel, WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT xlevel + 1, B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
) 
SELECT *
FROM GroupList
WHERE xlevel = 3
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
) 
SELECT IDENTITY( INT, 1, 1 ) rowId, *
INTO #tmp
FROM GroupList
ORDER BY WgParentWgID, WgID
GO

SELECT *
FROM #tmp
WHERE rowId = 3
GO


;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT *
FROM
    (
    SELECT ROW_NUMBER() OVER( ORDER BY WgParentWgID, WgID ) rowId, *
    FROM GroupList
    ) x
WHERE rowId = 3
ORDER BY rowId
GO

HTH

Best Answer

Related Solutions

Sql-server – How to get the first or last row in ordered result set, depending by column value

Sql-server – Get a specific row from a TSQL common table expression

Related Question