SQL Server

OFFSET FETCH in SQL Server 2012

In this post i will discuss what is OFFSET FETCH in SQL Server 2012 and where we can use it.

OFFSET-FETCH is a new feature in SQL Server 2012.It is an extension to ORDER BY clause.

It allows you to return a range of records by mentioning the starting point( an offset) and the number of rows you would like to fetch. In other words you can specify the number of rows to skip and number of rows to retrieve.

Consider the code below which explains what is OFFSET FETCH in SQL Server 2012
I have created a table named EMPLOYEE and inserted 100 records into the table using While loop.

USE DBName
GO
 
CREATE TABLE EMPLOYEE
(
ID INT,
NAME VARCHAR(20)
)

GO

 

DECLARE @I INT
SET @I=1
WHILE (@I<=100)
BEGIN
INSERT INTO EMPLOYEE VALUES (@I,‘NAME ‘+CONVERT(VARCHAR(3),@I))
SET @I=@I+1
END
 
GO

 

In the code written below I have mentioned to skip first 10 rows and then display next 5 rows.

 

SELECT * FROM EMPLOYEE
ORDER BY ID
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

The output of the script is shown in the following figure.

what-is-OFFSET-FETCH-SQL-Server-2012

This command will be very useful for the front end developers to display data page wise. For example a front end developer needs to display first 50 records on the first page and then next 50 records on second page and so on.

 

 

2 thoughts on “OFFSET FETCH in SQL Server 2012

  1. Hi Vikash
    Excellent posting. Just wondering, how could you extract sales data for weekdays sales ( Monday to Frioday ) , then skip saturday and sunday and then list again next 5 days , skip 2 days …for a yearly sales data
    any quick solution with this method?
    Regards

    1. There is a function datepart(dw,datecolumn) . This will return numbers 1,2,3,4,5,6,7. 1 represents Sunday , 2 represents Monday and so on till 7 which represents Saturday.

Leave a Reply

Your email address will not be published.