SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)

Sqlserver 2012 introduced OFFSET and FETCH clause to limit the number of rows returned by a query,after sorting the rows using ORDER BY clause.

 OFFSET clause sets how many rows needs to be skipped  

 FETCH NEXT n ROWS ONLY, displays the next n records

Note :  OFFSET and FETCH clause can be used only with ORDER BY clause

Examples for understanding  OFFSET  & FETCH clause 

USE AdventureWorks2012

--Example 1: - using only OFFSET  with  ORDER BY clause

--In below example,after sorting rows in ascending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROW

--Example 2 - In below example,after sorting rows in descending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID DESC
     OFFSET 1 ROW
 

--Example 3 - In below example,after sorting rows ,first row is skipped & next 10 rows are fetched.   

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROW
     FETCH NEXT 10 ROWS ONLY

--We can use OFFSET  & FETCH clause  in both ways
--OFFSET n ROW
--     FETCH NEXT n ROW ONLY
--(OR)

--OFFSET n ROWS
--     FETCH NEXT n ROWS ONLY

--Example 4 - In below example,total row count in table is 290,for testing if i give FETCH NEXT 300 ROWS ONLY skipping 1 row,it will fetch accordingly w.r.t table row count

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROWS
     FETCH NEXT 300 ROWS ONLY

 
--Example 5  -  Specifying variables for OFFSET & FETCH values

DECLARE @Rows_Skip_Count INT = 1,
        @Rows_Fetching_Count INT = 10
SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET @Rows_Skip_Count ROWS
     FETCH NEXT @Rows_Fetching_Count ROWS ONLY

--Example 6 - Specifying expressions for OFFSET & FETCH values

DECLARE @NoOfRows_Skip_Count INT = 1,
        @NoOfRows_Fetching_Count INT = 10
SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 0 + @NoOfRows_Skip_Count ROWS
     FETCH NEXT 0 + @NoOfRows_Fetching_Count ROWS ONLY

--Example 7 -  Specifying SELECT queries for OFFSET & FETCH values

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET (SELECT BusinessEntityID FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1) ROWS
     FETCH NEXT (SELECT BusinessEntityID FROM [HumanResources].[Employee] WHERE BusinessEntityID = 10) ROWS ONLY

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET (SELECT 1) ROWS
     FETCH NEXT (SELECT 10) ROWS ONLY


Reference - http://msdn.microsoft.com/en-us/library/ms188385.aspx