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 11th 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.
