SQL Server 2012 DENALI T-SQL Enhancements: FETCH & OFFSET

SQL Server 2012 DENALI T-SQL Enhancements: FETCH & OFFSET

posted  11/14/2011 12:35:07 PM  by Raunak Jhawar -  Views: [29071] 

• Email

T-SQL New feature for OFFSET and FETCH

Please note the table is completely fictitious and all data is just for presentation purpose

Consider a scenario below:

English Product Name	Reorder Point	Dealer price
Item 1	100	1000
Item 2	200	2000
Item 3	150	3000
Item 4	250	4000
Item 5	190	5000
Item 6	200	6000
Item 7	300	7000
Item 8	190	8000
Item 9	300	9000
Item 10	200	10000

 This represents a limited data set. Assume you have a wide array of data with you.

Now what if you were to list the rows from Row 150 to Row 350?

Before the latest function addition to Denali, I would have proposed writing a CTE, which would compute a ranking column using the windowing functions we already have in SQL Server and use the outcome of the CTE and filter the records from the range specified.

The SQL would have been like:

WITH product_cte 
     AS (SELECT adventureworksdwdenali.dbo.dimproduct.englishproductname, 
                adventureworksdwdenali.dbo.dimproduct.reorderpoint, 
                adventureworksdwdenali.dbo.dimproduct.dealerprice, 
                Row_number() OVER(ORDER BY 
                adventureworksdwdenali.dbo.dimproduct.englishproductname) AS 
                   [Current_Order] 
         FROM   adventureworksdwdenali.dbo.dimproduct) 
SELECT * 
FROM   product_cte 
WHERE  product_cte.current_order BETWEEN 151 AND 350

Can this be simplified?

Yes, with Denali the SQL Server Team has provided a new added and certainly promising feature which  

Actually “smoothens” the entire process. No CTE. No windowing functions. Check this out.

The SQL would have been like:

select AdventureWorksDWDenali.dbo.DimProduct.EnglishProductName 
       , AdventureWorksDWDenali.dbo.DimProduct.ReorderPoint 
       , AdventureWorksDWDenali.dbo.DimProduct.DealerPrice 
from AdventureWorksDWDenali.dbo.DimProduct 
order by EnglishProductName 
offset 150 rows 
fetch next 350 rows only

Let us demystify the syntax

•  Select clause remains the same, no rocket science
•  Offset signifies the new pointer where the data now has to fetch and the fetch clause states the number of rows that are desired on the result pane. That’s all!!

Free take away:

 The order by clause is a mandatory and cannot be skipped. If you do, you are presented with an error message which resembles as below (as  on my computer screen J)

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '150'.

Msg 153, Level 15, State 2, Line 5

Invalid usage of the option next in the FETCH statement.

 A TOP n clause can be specified if one were to display the top/bottom n records

The SQL would be

SELECT TOP 100 adventureworksdwdenali.dbo.dimproduct.englishproductname, 
               adventureworksdwdenali.dbo.dimproduct.reorderpoint, 
               adventureworksdwdenali.dbo.dimproduct.dealerprice 
FROM   adventureworksdwdenali.dbo.dimproduct 
ORDER  BY englishproductname

And similarly this can be composed as

select AdventureWorksDWDenali.dbo.DimProduct.EnglishProductName
       , AdventureWorksDWDenali.dbo.DimProduct.ReorderPoint 
       , AdventureWorksDWDenali.dbo.DimProduct.DealerPrice
from AdventureWorksDWDenali.dbo.DimProduct 
order by EnglishProductName
offset 0 rows
fetch next 100 rows only

This is one of many applications where one can use the added feature. Happy learning!!

Thanks,

Raunak