Using OFFSET N Rows Fetch Next N Rows in Sql server

Friends,

Don’t get worried that you have never used or seen these functions in SQL SERVER. This is newly introduced in 2012 version i.e Denali. The major advantage of this function is PAGINATION. We can implement pagination at Sql Server level. Let’s See a simple example to demonstrate these functions.

Let’s say my requirement here is to get 25th record to 50th record from the table order by date. That means I have to skip first 25 rows and all the rows after 50th row. To achieve the same we generally use CTE(Common table expression) in earlier versions of Sql Server and see the sample query below.

WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY date) AS Row_Count,*
FROM tablename)
SELECT * FROM cte
WHERE Row_Count between 25 and 50

The above query Orders the data by DATE and creates a ROW_NUMBER for all the rows from which we are selecting ROWS with number between 25 and 50 which will fulfill the requirement. But, if the see the query, it’s bit complex and not a normal select statement. If you are not good in Sql Server CTE’s then you are gone case. PFB the query which achieves the same result with out using CTEs and by using OFFSET and FETCH in Sql Server Denali.

SELECT *
FROM tablename
ORDER BY date
OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;

The above query looks simple and easy to understand. The OFFSET function skips the # of rows specified and the FETCH next pulls the # of rows provided. If you wish to pull all the records except the top 25 then you can use the query given below.

SELECT *
FROM tablename
ORDER BY date
OFFSET 25 ROWS ;

If you wish to have a dynamic value to the query then you can do it by defining variables.

DECLARE @value int = 25
SELECT *
FROM tablename
ORDER BY date
OFFSET @Value ROWS FETCH NEXT @Value ROWS ONLY;

The advantage of the above method is that we can write Select Statement as per our wish and apply pagination with JUST one extra line of code.

Hope you understood the concept and don’t forget to use OFFSET and FETCH when you work on Denali to apply pagination.

All the Best !!

Regards,
Roopesh Babu V