Blog Pages

SQL Paging in SQL Server 2011

ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY
Available only from SQL Server 2011 (Denali).

SELECT ..... FROM .....
ORDER BY ColumnA
  OFFSET 10 ROWS -- skip the first 10 rows
  FETCH NEXT 10 ROWS ONLY --return only 10 rows

Until MSSQL 2011 we do 'Paging' using:
SELECT ..., ROW_NUMBER ( ) OVER ( ORDER BY columnA) AS ROW FROM ... WHERE ROW BETWEEN 51 AND 100

 And here a sample to SP that return a page in paging list:
CREATE PROCEDURE SqlPagingSP
(
  @PageNumber int,
  @RowsPerPage int
)
AS

SELECT .....
FROM ......
ORDER BY ColumnA
  OFFSET (@PageNumber-1)*@RowsPerPage ROWS
  FETCH NEXT @RowsPerPage ROWS ONLY
GO

No comments:

Post a Comment