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.
CREATE TABLE EMPLOYEE
DECLARE @I INT
INSERT INTO EMPLOYEE VALUES (@I,‘NAME ‘+CONVERT(VARCHAR(3),@I))
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.
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.
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?
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.