OFFSET and FETCH Feature of SQL Server

 

OFFSET & FETCH Feature of SQL Server

 

The OFFSET and FETCH clause of SQL Server  provides you an option to fetch only a page or a window of the results from the complete result set.

I.e. If I want to see only 5 rows from 100^th row I can use Offset-Fetch feature of SQL server

Exercise:

•  We will use the OFFSET and FETCH feature of SQL Server 2012.
•  We will also show how you can implement SQL Server Paging or SQL data page retrieval using this new feature.

Exercise 1:

We will first find count of rows in person table and then we will see how we can use offset-fetch feature.

/***********************Query / output 1**********************************/

use [AdventureWorks2012]

GO

Select count (*) as [count of row] FROM Person.Person

/************************ Query / output 2*********************************/

 

use [AdventureWorks2012]

GO

SELECT top 5

  BusinessEntityID

  ,PersonType

 ,FirstName + ' ' + MiddleName + ' ' + LastName

FROM Person.Person

 ORDER BY BusinessEntityID ASC

GO

/************************* Query / output 3********************************/

 

use [AdventureWorks2012]

GO

SELECT

  BusinessEntityID

  ,PersonType

 ,FirstName + ' ' + MiddleName + ' ' + LastName

FROM Person.Person

 ORDER BY BusinessEntityID ASC

  OFFSET 100 ROWS

  FETCH NEXT 5 ROWS ONLY

Exercise 2:

Let’s create a stored procedure to use paging feature for my Algae application. Please execute below procedure

/************************* Query 4********************************/

 

USE [AdventureWorks2012]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[ExampleSQL2012PagingFeature]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[ExampleSQL2012PagingFeature]

GO

CREATE PROCEDURE ExampleSQL2012PagingFeature

 (   @PageNo INT,  @RowCountPerPage INT  )

AS

SELECT

  BusinessEntityID

 ,PersonType

 ,FirstName + ' ' + MiddleName + ' ' + LastName

FROM Person.Person

 ORDER BY BusinessEntityID

  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS

  FETCH NEXT @RowCountPerPage ROWS ONLY

GO

/************************Output 4*********************************/

 

Once Procedure is executed, this procedure execution will give us five records starting at page 21 where the records are ordered by BusinessEntityID. But why, because first 100 (20 pages * 5 rows per page = 100) rows were discarded and the stored procedure fetched only the next 5 rows thereby limiting the number of rows sent to the Algae client page.