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:


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:


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)

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

The SQL would be

And similarly this can be composed as

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



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