How to use OFFSET and FETCH Clause in SQL Server

How to Offset N rows and Fetch next N rows from a Table in SQL Server
The OFFSET and FETCH clause of SQL Server 2012 provides you an option to fetch only a page or a window or a subset of results from the complete result set. The OFFSET-FETCH can be used only with the ORDER BY clause.
OFFSET :
The OFFSET Clause is used to specify the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.

FETCH :
The FETCH Clause can be used along with OFFSET to specify the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

Limitations in Using OFFSET-FETCH
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.

Example:

Suppose we have the Table "Tbl_Sample" as follows..

From this Table, we can select by excluding(OFFSET) the first 3 rows as follows..

Select * From [dbo].[tbl_Sample]

Order By Order_Id

OFFSET  3 ROWS;

Now we can select the next 5 rows after excluding the first 3 rows as follows..

Select * From [dbo].[tbl_Sample]
Order By Order_Id
OFFSET  3 ROWS
FETCH  NEXT  5 ROWS ONLY;

User defined Function to Fetch N rows by Offsetting M rows from a Table :
We can use the below User defined Function to Fetch N rows by Offsetting M rows from the above table.
CREATE Function udf_OffsetFetchRows 
(@m Int, @n Int) Returns table
As
Return
(
 Select * From  [dbo].[tbl_Sample] Order By NetSales Desc
OFFSET @m rows
FETCH Next @n rows only
)
GO

Result :
Select * From udf_OffsetFetchRows (6,2)
This will Offset 6 rows and Fetches next 2 rows from results the sorted Desc by NetSales.

----------------------------------------------------------
Now we will create a stored procedure which will use the OFFSET and FETCH feature of SQL Server 2012 to achieve sql paging while displaying results. In this stored procedure we are passing in a page number and the number of rows to return.

IF Exists (Select * From sys.objects 

Where object_id =OBJECT_ID(N'[dbo].[OffsetPagingFetchRows]') 

                             AND Type in (N'P', N'PC'))
Drop Procedure [dbo].[OffsetPagingFetchRows]
GO
CREATE Procedure [dbo].[OffsetPagingFetchRows]
 (
  @PageNo INT,
 @RowCountPerPage INT
 )
AS
BEGIN
 Select * From [dbo].[tbl_Sample]
 Order By Order_Id
 OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
 FETCH NEXT @RowCountPerPage ROWS ONLY
END
GO

Now we will execute the stored procedure using the below command. This will give us 5 records starting at page 3 where the records are ordered by Order_Id.

EXECUTE [OffsetPagingFetchRows] 3, 5

Notes :

You can see that in first 15(2 pages * 5 rows per page = 10) rows were discarded/excluded and the stored procedure fetched only the next 5 rows thereby limiting the number of rows.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------