Pages

Friday, May 9, 2014

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 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 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) ASRNUM,
               STUDROLL, STUDNAME
        FROM   tbl_student)AS a
WHERE  a.RNUM AND a.RNUM 5        

Hope you like it.

No comments:

Post a Comment