SQL Server 2012 DENALI T-SQL Enhancements

T-SQL Enhancement in SQL Server 2012 – Denali

The OFFSET & FETCH features in SQL Server 2012 – Denali

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!!

Talk to us

Leave a Reply