OFFSET and FETCH - New T-SQL Paging feature in SQLServer 2012

Microsoft SQL Server 2012 introduces new T-SQL feature called OFFSET and FETCH with the ORDER BY clause.

It helps to fetch only a set of rows from the complete result set window. This also helps in SQL Paging which improves performance while retrieving and displaying large number of records from SQL Server Database. Using this feature, developers can do paging from the back end itself.

In earlier versions of SQL Server 2005/2008, we were using complex query to perform SQL Data paging. Here is the sample code to perform SQL data paging in earlier versions.

USE EmployeeDB

GO

SELECT *

FROM   (

             SELECT

                    ROW_NUMBER() OVER(ORDER BY Id) AS rownum,

                    [Name],

                    [salary],

                    [ReportsTo]

             FROM

                    Employee

             ) AS Emp

WHERE  rownum > 2 AND rownum <= 5

Now it has become very easy in SQL Server 2012. We need to use only two keywords (OFFSET and FETCH) with ORDER BY Clause. Here is the sample code in SQL Server 2012 to perform SQL data paging.

USE EmployeeDB

GO

SELECT

       Id,

       [Name],

       [salary],

       [ReportsTo]

FROM

       Employee

ORDER BY Id

OFFSET 2 ROWS

FETCH NEXT 3 ROWS ONLY

OFFSET clause defines how many records need to be excluded. 

FETCH NEXT clause defines how many records need to be picked up after exclusion.

In the above example, SQL excludes first 2 records and will pick up 3 records afterwards.

Whenever we need to perform paging, two things have to be considered.

1.    Page No.

2.    Number of Records.

Here OFFSET is for page number and FETCH NEXT is the number of records in each page.

We cannot use FETCH NEXT without OFFSET Clause.

Here is the simple example to perform data paging in SQL Server 2012.

USE EmployeeDB

GO

CREATE PROCEDURE dbo.SP_SQL_Paging

@PageNo int,

@RecordsPerPage int

AS

SELECT

       Id,

       [Name],

       [salary],

       [ReportsTo]

FROM

       Employee

ORDER BY Id

OFFSET (@PageNo-1)*@RecordsPerPage ROWS

FETCH NEXT @RecordsPerPage ROWS ONLY

GO

The below execution result displays the 1^st page with 1^st 10 records

SP_SQL_Paging 1,10 –1^st Page

GO

The below execution result displays the 2^nd page with 2^nd 10 records

SP_SQL_Paging 2,10 –2^nd Page

GO

The below execution result displays the 3^rd page with 3^rd 10 records

SP_SQL_Paging 3,10 –3^rd Page

GO