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

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

1_SQL_Server2012_DENALI_T-SQL_Enhancements_FETCH_OFFSET

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

2_SQL_Server2012_DENALI_T-SQL_Enhancements_FETCH_OFFSET

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

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

7 Comments on “SQL Server 2012 DENALI T-SQL Enhancements: FETCH & OFFSET”

  1. Sarab, I do not understand the need of “ONLY” keyword at the end of the SQL statement. This really is not required?

  2. May be they did it just to make it more readable….. As you know, Microsoft always belived in simplicity and T-SQL is more readable than others.

  3. Hi Raunak – great post and lovely new Denali feature! Question though, meaning I’ve probably misunderstood something, but in the original cte we are filtering where current order is between 151 and 350 I.E. 200 rows. However in the translated offset/fetch example we state an offset of 150 and a fetch of 350, would this not give us back 350 rows instead of the 200?

  4. Eagle eyes Andy, The SQL is wrongly composed.

    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 200 rows only

    Thanks for pointing the error!

    Thanks,
    Raunak

  5. One more point I would like to raise over here is that while using the CTE we are fetching the rows from 150 to 250 (i.e. a count of 200) whereas in the fetch/offset method, saying offset 150 would start selecting the records from row 151. To select from 150 we will have to specify offset 149.

  6. Yes, because the SQL is asking the SQL engine to offset 150 rows and hence the first record will begin from 151. Valid point.

    Thanks\Raunak

Leave a Reply

Your email address will not be published. Required fields are marked *