SQL - OFFSET and FETCH values

The following example specifies an integer constant as the value for the OFFSET and FETCH clauses. The first query returns all rows sorted by the column DepartmentID. Compare the results returned by this query with the results of the two queries that follow it. The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows. The final 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.

Example 1

USE AdventureWorks2012;

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;

Example 2

USE AdventureWorks2012;

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;

Example 3

USE AdventureWorks2012;

GO

-- Specifying expressions for OFFSET and FETCH values   

DECLARE @StartingRowNumber tinyint = 1

      , @EndingRowNumber tinyint = 8;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

    OFFSET @StartingRowNumber - 1 ROWS

    FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

Example 4

-- Specifying a constant scalar subquery

USE AdventureWorks2012;

GO

CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);

GO

INSERT INTO dbo.AppSettings VALUES(1, 10);

GO

DECLARE @StartingRowNumber tinyint = 1;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

    OFFSET @StartingRowNumber ROWS

    FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;