OFFSET and FETCH in SQL 2012

Introduction

Hello friends after long time, I am trying to post something. It's related to MS SQL Server 2012 new T-SQL features called OFFSET and FETCH.

As it is introduce new at SQL 2012 but logic behind is OLD as we can do it by previous version of MS SQL. But before it is Littlemore tedious jobs. Here in MS SQL 2012 we can do in on the fly.

First Understand What is OFFSET and FETCH

Suppose we have a table objects called tbl_student

STUDROLL	STUDNAME
1	Joydeep
2	Tanay
3	Archita
4	Sudeshna
5	Tapash
6	Palash

Now we understand what OFFSET done. OFFSET provides a starting row from which to display the result set. FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point.

We must remember that OFFSET and FETCH only worked with ORDER BY clause only.

Take a Simple Example to understand it

SELECT STUDROLL, STUDNAME

FROM   tbl_student

ORDER BY STUDROLL

OFFSET 0 ROWS

FETCH NEXT 2 ROWS ONLY

So what it display as result

STUDROLL	STUDNAME
1	Joydeep
2	Tanay

Now

SELECT STUDROLL, STUDNAME

FROM   tbl_student

ORDER BY STUDROLL

OFFSET 2 ROWS

FETCH NEXT 2 ROWS ONLY

So what it display as result

STUDROLL	STUDNAME
3	Archita
4	Sudeshna

Hope now we understand it.

How it worked with previous Version

SELECT a.STUDROLL, a.STUDNAME

FROM   (SELECT ROW_NUMBER() OVER (ORDER BY STUDROLL) AS RNUM,

               STUDROLL, STUDNAME

        FROM   tbl_student)AS a

WHERE  a.RNUM > 2 AND a.RNUM < 5        

Hope you like it.

Posted by: MR. JOYDEEP DAS