SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012

By: Editor
April 12, 2011

Introduction

Microsoft SQL Server 2012 introduces an interesting clause namely OFFSET and FETCH to the existing ORDER BY clause. Using OFFSET and FETCH clause developers can write TSQL code to fetch only a set of rows from the complete result set window. This will help developers going forward achieve SQL Server Pagination there by improving performance when retrieving and displaying large number of records from SQL Server Database.

Example SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012

Let us go through an example which illustrates achieving SQL Server Pagination Using OFFSET and FETCH feature in SQL Server 2012.

Use AdventureWorks2008R2
GO

SELECT
           
 PP.FirstName + ' ' + PP.LastName AS 'Name'
           
,PA.City
           
,PA.PostalCode

FROM
  Person.Address PA
           
INNER JOIN
                       
Person.BusinessEntityAddress PBEA
                                   
ON PA.AddressID = PBEA.AddressID
           
INNER JOIN
            
           Person.Person PP
                                   
ON PBEA.BusinessEntityID = PP.BusinessEntityID

ORDER
BY PP.FirstName
 
          OFFSET 0 ROWS
               
FETCH NEXT 5 ROWS ONLY



If you carefully look at the ORDER BY clause in the above TSQL code you would see that I have used “OFFSET 0 ROWS” and “FETCH NEXT 5 ROWS ONLY” extension to ORDER BY clause. Using OFFSET and FETCH NEXT ORDER BY clause developer going forward in SQL Server 2012 can write stored procedures which can retrieve only specific set of rows rather than retrieving the entire query result. This feature will improve the query performance and get only the required result set.

Result Using Offset and Fetch Feature of SQL Server 2012

Example – Achieve SQL Server Pagination Using OFFSET and FETCH Feature in SQL Server 2012

In the below example you would see that I have created a stored procedure namely PersonNameAndLocationUsingSQLServerPagingFeature which will help me achieve SQL Server Pagination by leveraging OFFSET and FETCH feature in SQL Server 2012.

Use AdventureWorks2008R2
GO
 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
    OBJECT_ID
(N'[dbo].[PersonNameAndLocationUsingSQLServerPagingFeature]') AND type in (N'P', N'PC'))
DROP
PROCEDURE [dbo].[PersonNameAndLocationUsingSQLServerPagingFeature]
GO
 

CREATE PROCEDURE PersonNameAndLocationUsingSQLServerPagingFeature
(

           
@StartingRowNumber    INT,
           
@RowCountPerPage      INT
 
)

AS
BEGIN

    SELECT

           
 PP.FirstName + ' ' + PP.LastName AS 'Name'
           
,PA.City
           
,PA.PostalCode

    FROM
  Person.Address PA
           
INNER JOIN
                       
Person.BusinessEntityAddress PBEA
                                   
ON PA.AddressID = PBEA.AddressID
           
INNER JOIN
                       
Person.Person PP
                                   
ON PBEA.BusinessEntityID = PP.BusinessEntityID

    ORDER
BY PP.FirstName
             
OFFSET (@StartingRowNumber - 1) * @RowCountPerPage ROWS
           
              FETCH NEXT @RowCountPerPage ROWS ONLY

END
GO

/* Leveraging SQL Server Pagination Using OFFSET and FETCH NEXT Feature in SQL Server 2012 */
Use
AdventureWorks2008R2
GO

EXEC PersonNameAndLocationUsingSQLServerPagingFeature 1,10
GO

SQL Server Paging using Offset and Fetch Feature of SQL Server 2012

Conclusion

The OFFSET and FETCH NEXT clause which is an extension to existing ORDER BY clause in SQL Server 2012 will help developers achieve SQL Server Paginiation. This feature will be widely used by SQL Server Developers to pull only required number of records which they would like to display in ASP.NET Data Grid rather than pull the entire set of records which are matching the requirement. Implementation of this feature within a stored procedure will help the databas query run faster and only reterive required number of rows.

Reference: ORDER BY Clause (Transact-SQL)


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."