Tuesday, 6 November 2012

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 1st page with 1st 10 records

SP_SQL_Paging 1,10 –1st Page
GO

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

SP_SQL_Paging 2,10 –2nd Page
GO

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

SP_SQL_Paging 3,10 –3rd Page
GO