Pagination with SQL Server 2011 (Denali)

by Amol 24. February 2011 09:04

One of T-SQL enhancement in SQL Server 2011 is Pagination. You can control number of rows in a resultset. In earlier version we used to use TOP (n) keyword to control number of rows in a resultset. But TOP (n) doesn't have control from which row you want to have resulset and finally how many rows you want to return.

Microsoft SQL Server 2011 has introduce some enhancement in ORDER BY clause. In earlier versions of SQL Server we were able to use ORDER BY for having sorted resultset by specifying ascending and descending order, We were able to sort the resultset in conditional order, also ORDER BY is used in ranking function as well. Apart from all these functionalities, SQL Server 2011 has introduced OFFSET and FETCH options for ORDER BY Clause.

Let see how OFFSET and FETCH are getting used along with ORDER BY.

Specifying integer constants for OFFSET and FETCH values

The following example specifies an integer constant as the value for the OFFSET and FETCH clauses.
First query returns all rows sorted by the column DepartmentId.
The second query uses the clause OFFSET 5 ROWS, to skip the first 5 rows and return all remaining rows.
The third query uses the clause OFFSET 0 ROWS to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

USE AdventureWorks2008R2;
GO
-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

Specifying variables for OFFSET and FETCH values

The following example declares the variables @StartingRowNumber and @FetchRows and specifies these variables in the OFFSET and FETCH clauses.

USE AdventureWorks2008R2;
GO
-- Specifying variables for OFFSET and FETCH values 
DECLARE @StartingRowNumber tinyint = 1
      , @FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
    OFFSET @StartingRowNumber ROWS
    FETCH NEXT @FetchRows ROWS ONLY;

Note: Examples are used from SQL Server BOL.

Tags:

SQL Server Denali

Category

Recent Posts

Tag cloud