Wednesday, October 15, 2014

Sql Server perform pagination on the returned results

Variant 1 (Sql server 2008 and upper)

SELECT * FROM
(
      SELECT *,
        ROW_NUMBER() OVER (ORDER BY PK_ID) as rowNum
      FROM  WHERE Data_rap > '2014-01-01'
) sub
WHERE rowNum > 9
  AND rowNum <= 9 + 20

 
Limitations
 - make a subquery
 - ORDER BY is mandatory

Variant 2 (Sql server 2012 and upper)

SELECT * FROM WHERE Data_rap > '2014-01-01'
ORDER BY PK_ID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;


Limitations

 - ORDER BY is mandatory to use OFFSET and FETCH clause.

 - OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

 - TOP cannot be combined with OFFSET and FETCH in the same query expression.

 - The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

No comments:

Post a Comment