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!!
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
Amazing feature and easy than Cursors and CTEs.
Sarab, I do not understand the need of “ONLY” keyword at the end of the SQL statement. This really is not required?
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.
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?
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
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.
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