Ad-Hoc Query Paging

The SQL Server 2012 code named "Denali" has lot of new feature. In this article I am trying to explain a new feature of SQL Server 2012 called "Ad-Hoc Query Preparing"

The ORDER BY clause of SELECT statement has been enhanced by SQL Server 2012 by using the combination of OFFSET and FETCH.  OFFSET and FETCH along with ORDER BY gives us the option to paging through the result set.

The OFFSET clauses specifies the numbers of rows to skip before returning the query and the FEATCH specifies the numbers of rows to return after OFFSET clause has been proceed.

Let's takes an example to understand it properly. 

Suppose we have a table named TABLE-A.  The structure of the TABLE-A is mentioned bellow. The TABLE-A Contains more then 10,000 records.

STUDNAME	STUCLASS
JOYDEEP DAS	1
PALASH KANTI PAUL	1
SUDIP DAS	1
TUHIN KUMAR SHINAH	1
SANGRAM JIT BHATTERCHARYA	1
N th …

 

Now we want to display the result set like this.

First remove the 10 records and from 11^th to next 10 records to be displayed in the result set.

Frist I am providing a query that supports SQL 2005 to display the desired result st using CTE.

WITH stud(stuname, stuclass, rowNUM)

AS 

(SELECT Row_number() OVER (ORDER BY p.stuname) AS rowNUM,

        stuname, stucalss

 FROM  TABLE-A p

)SELECT *

 FROM   stud p

 WHERE  p.rowNUM BETWEEN 11 AND 20

It is quite complicated for the developer who uses the SQL Server 2012…. What you think?

So now I am providing you the new Example using OFSET and FEATCH NEXT feature of SQL 2012.

SELECT p.stuname, p.stuclass

FROM   TABLE-A p

ORDER  BY p.stuname

OFFSET 10 rows

FETCH NEXT 10 rows only

So it is so easy now with SQL Server 2012.

Hope you like it.

Posted by: MR. JOYDEEP DAS