Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Overview of OFFSET and FETCH Feature of SQL Server 2012


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Paging

Problem

While looking through the new features in SQL Server 2012 I came across a new feature OFFSET and FETCH. The OFFSET and FETCH clause of SQL Server 2012 provides you an option to fetch only a page or a window of the results from the complete result set. In this tip we will take a look at an example which uses the OFFSET and FETCH feature of SQL Server 2012. Also, we will show how you can implement SQL Server Paging or SQL data page retrieval using this new feature.

Solution

Using this feature of SQL Server 2012 one can easily implement SQL Server Paging while displaying results to the client. We will take a look at simple example and then also how you could construct a stored procedure to implement SQL paging.

Let's go through a simple example which demonstrates how to use the OFFSET and FETCH feature of SQL Server 2012. You can see below that the TSQL looks the same as what you write today except after the ORDER BY clause we have the OFFSET and FETCH commands. One thing to note is that you have to use an ORDER BY to use this feature. The OFFSET basically tells SQL to skip the first 100 rows and the FETCH will get the next 5 rows.

USE AdventureWorks2008R2
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 100 ROWS 
  FETCH NEXT 5 ROWS ONLY
GO

The below snippet shows the output when running the above commands. This shows that the first 100 rows were discarded and the query fetched the next 5 rows in the complete recordset.

the offset and fetch feature in sql server denali

Let's go through another example where 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 to client machines. In this stored procedure we are passing in a page number and the number of rows to return. These values are then computed to get the correct page and number of rows.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]
GO
CREATE PROCEDURE ExampleUsageOfSQLServerDenaliPagingFeature
 (
  @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

Let's go ahead and execute the stored procedure using the below command. This will give us five records starting at page 21 where the records are ordered by BusinessEntityID.

/* Display Records Between 101 AND 105 BusinessEntityID */
EXECUTE ExampleUsageOfSQLServerDenaliPagingFeature 21, 05
GO

The below snippet shows the output once the above stored procedure is executed successfully. You can see that 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 client.

achieve sql server paging using the offset and fetch feature frame of sql server denali

You have seen in this tip how easily you can achieve SQL Server Paging using the OFFSET and FETCH feature of SQL Server 2012. SQL paging is not as hard as it used to be with this new feature.

Next Steps


Last Update:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, April 13, 2016 - 5:43:42 AM - Umesh P Back To Top

Very Very informative article. 

I got clear understanding of feature. Thnaks a lot. 

 


Thursday, May 15, 2014 - 7:40:41 AM - Karl Rhodes Back To Top

We're testing our SPs with Offset and Fetch to page a sample table in our DB that has approximately 113000 records.

If we page our SP so it will return 1000 records per page, then close to the start of our record set, we're getting our records back in approximately 200 milliseconds. However, as we get further down the pages, closer to the end of the record set, then slows right down to 2.5 seconds for the 100th page.

The pages get slower and slower as you move away from the first page.

Is there anything that can be done to speed this up?


Thursday, April 18, 2013 - 6:18:16 PM - Dotnet Back To Top

HI, I am using sql server 2012 not DENALI...Getting an erros with OFFSET and Fetch Next..

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near 'OFFSET'.

Msg 153, Level 15, State 2, Line 4

Invalid usage of the option FIRST in the FETCH statement.

 
 
Help me out

Tuesday, September 25, 2012 - 6:49:28 PM - yarex Back To Top

This is a nce feature, but in real world apps i need to  know the total amount of records in order to calculate last page

to know when to stop.


Learn more about SQL Server tools