New T-SQL features in SQL Server 2012 – OFFSET and FETCH

There are solutions to this problem in other versions of the product in the form of temp tables, ROW_NUMBER() and TOP but I prefer OFFSET and FETCH to the others – it’s just simple! :)

I am using SQL Server 2012 Express and the AdventureWorks 2012 database for this demonstration.

So lets look at some data and I have decided to query some of the fields from the TransactionHistory table under the Production schema.

1 SELECT TOP 10
2 TransactionID
3 , ProductID
4 , TransactionDate
5 , Quantity
6 , ActualCost
7 FROM
8 Production.TransactionHistory;
TransactionID ProductID   TransactionDate         Quantity    ActualCost
------------- ----------- ----------------------- ----------- ---------------------
100000        784         2007-09-01 00:00:00.000 2           0.00
100001        794         2007-09-01 00:00:00.000 1           0.00
100002        797         2007-09-01 00:00:00.000 1           0.00
100003        798         2007-09-01 00:00:00.000 1           0.00
100004        799         2007-09-01 00:00:00.000 1           0.00
100005        800         2007-09-01 00:00:00.000 1           0.00
100006        801         2007-09-01 00:00:00.000 1           0.00
100007        954         2007-09-01 00:00:00.000 1           0.00
100008        955         2007-09-01 00:00:00.000 1           0.00
100009        966         2007-09-01 00:00:00.000 1           0.00

(10 row(s) affected)

This table contains approximately 133,500 rows – not a massive amount in today’s world but for the purposes of this article, lets say I wanted to write queries to page through this data sorted by newest transactions and I wanted to display 20 rows per page.

Using OFFSET and FETCH in SQL Server 2012

So here is an example. Note that OFFSET and FETCH are added after the ORDER BY clause.

01 SELECT
02 TransactionID
03 , ProductID
04 , TransactionDate
05 , Quantity
06 , ActualCost
07 FROM
08 Production.TransactionHistory
09 ORDER BY TransactionDate DESC
10 OFFSET 0 ROWS
11 FETCH NEXT 20 ROWS ONLY;

OFFSET provides a starting row from which to display the result set. FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point.

Note that NEXT or FIRST can be supplied for FETCH and are synonyms for ANSI compatibility. You can also type ROW or ROWS, again they are synonyms for ANSI compatibility.

So this is nice and easy, for the next page of results, the OFFSET value would be changed to 20 and then 40 etc.

OFFSET and FETCH can accept variables so for example:

01 DECLARE @OffsetRows tinyint = 0
02 , @FetchRows tinyint = 20;
03 SELECT
04 TransactionID
05 , ProductID
06 , TransactionDate
07 , Quantity
08 , ActualCost
09 FROM
10 Production.TransactionHistory
11 ORDER BY TransactionDate DESC
12 OFFSET @OffsetRows ROWS
13 FETCH NEXT @FetchRows ROWS ONLY;

You can use expressions in OFFSET and FETCH:

1 ORDER BY TransactionDate DESC
2 OFFSET @OffsetRows - 0 ROWS
3 FETCH NEXT @FetchRows - @OffsetRows + 1 ROWS ONLY;

And I really like this – plugging in a scalar sub queries:

01 SELECT
02 TransactionID
03 , ProductID
04 , TransactionDate
05 , Quantity
06 , ActualCost
07 FROM
08 Production.TransactionHistory
09 ORDER BY TransactionDate DESC
10 OFFSET @OffsetRows ROWS
11 FETCH NEXT (SELECT 20) ROWS ONLY;

So imagine that (SELECT 20) was in fact reading a table somewhere in your system (SELECT PageSize FROM PageSetting WHERE SiteID = 5) which controlled the number of rows to be displayed for each query.

OFFSET and FETCH versus ROW_NUMBER()

I’m not going to go into detail about all the methods of paging which have been employed in previous versions of SQL Server and start drawing comparisons and conclusions over performance (perhaps I will in a future post) but the one which immediately springs to mind as an alternative to OFFSET and FETCH is ROW_NUMBER()

So a quick comparison between the two methods shows the following:

Using OFFSET and FETCH

01 SELECT
02 TransactionID
03 , ProductID
04 , TransactionDate
05 , Quantity
06 , ActualCost
07 FROM
08 Production.TransactionHistory
09 ORDER BY TransactionDate DESC
10 OFFSET 0 ROWS
11 FETCH NEXT 20 ROWS ONLY

Using ROW_NUMBER() with CTE

01 WITH Paging_CTE AS
02 (
03 SELECT
04 TransactionID
05 , ProductID
06 , TransactionDate
07 , Quantity
08 , ActualCost
09 , ROW_NUMBER() OVER (ORDER BY TransactionDate DESCAS RowNumber
10 FROM
11 Production.TransactionHistory
12 )
13 SELECT
14 TransactionID
15 , ProductID
16 , TransactionDate
17 , Quantity
18 , ActualCost
19 FROM
20 Paging_CTE
21 WHERE RowNumber > 0 AND RowNumber <= 20

So what do you think? It’s certainly easier to write a query using OFFSET and FETCH as there is less involved. There is one less column too because “RowNumber” is not needed for the OFFSET and FETCH version.

If I were a betting man, I would say that the execution plans are different between the two queries. So lets take a look.

Using OFFSET and FETCH…

sql server offset and fetch versus row_number()

Using ROW_NUMBER()…

sql server offset and fetch versus row_number()
There is certainly more going on in the second one right? As I wrote above, I’m not intending to draw any conclusions on this because I am not doing any thorough testing here.

Finally, if you want to guarantee stable results in your OFFSET and FETCH paging solution there are two things that you must implement.

  1. You should ensure that the underlying data does not change and that involves running the queries inside a transaction using either snapshot or serializable transaction isolation.
  2. The ORDER BY clause needs to contain a column or combination of columns that are guaranteed to be unique.

    Limitations in Using OFFSET-FETCH

    • ORDER BY is mandatory to use OFFSET and FETCH clause.
    • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
    • TOP cannot be combined with OFFSET and FETCH in the same query expression.
    • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
    •  

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s