26

I have few questions in context of the older row_number (SQL Server 2008) and the newer OFFSET + FETCH (SQL Server 2012) paging mechanism provided by SQL Server 2012.

  1. What are the limitations with row_number()?
  2. Is OFFSET + FETCH an improved replacement for row_number()?
  3. Are there any use-cases which could only be sufficed using one and not the other?
  4. Are there any performance differences between the two? If yes, which one is recommended?

Thanks.

Anand Patel
  • 6,031
  • 11
  • 48
  • 67
  • 3
    Have you checked around? See this rather comprehensive answer: http://dba.stackexchange.com/questions/30210/why-are-there-execution-plan-differences-between-offset-fetch-and-the-old-st – Marcel N. May 05 '13 at 07:34

3 Answers3

20

Using ROW_NUMBER() works fine - it's just more work than necessary; you need to write a "skeleton" CTE around your actual query, add the ROW_NUMBER() column to your output set, and then filter on that.

Using the new OFFSET / FETCH is simpler - and yes, it's also better for performance, as these two links can show you:

So overall: if you're using SQL Server 2012 - then you should definitely use OFFSET/FETCH rather than ROW_NUMBER() for paging

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • This look quite honest, but I wonder how can I force EF use ofsets with 2012 sql server? – Johnny_D Nov 01 '13 at 11:01
  • I have several reasons to not agree with your conclusion, it is not as straightforward as you put it: The first link you mention does not do any performance comparison: _I’m not intending to draw any conclusions on this because I am not doing any thorough testing here._ The 2nd link does not mention what is the number of rows the test was performed on, and the OFFSET/FETCH approach is susceptible of being poor with large tables (see here http://www.mssqlgirl.com/paging-function-performance-in-sql-server-2012.html) – lcfd Sep 26 '18 at 14:15
  • @lcfd: That mssqlgirl article doesn't compare OFFSET/FETCH with ROW_NUMBER: it only compares it with techniques that involve ordering by a known, unique, indexed, consecutive ID field. That's not applicable to many real-world situations. – StriplingWarrior Jan 09 '19 at 18:05
2

By definition ROW_NUMBER is a temporary value calculated when the query is run. OFFSET / FETCH is an option that you can specify for the ORDER BY clause.

In terms of speed, they both achieve great performance and the difference between each method depends on the columns that you specify in the SELECT clause and the Indexes that you have on your tables.

In the following 2 examples, you can see a difference between the two methods:

1. Case when OFFSET / FETCH is faster:

SELECT
    Id
FROM Orders
ORDER BY
    Id

OFFSET 50000 ROWS FETCH NEXT 5000 ROWS ONLY

SELECT
    A.Id
FROM
(
    SELECT
        Id,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Orders
) AS A
WHERE
    A.RowNumber BETWEEN 50001 AND 55000

2. Case when ROW_NUMBER() is faster:

SELECT
    *
FROM Orders
ORDER BY
    Id
OFFSET 50000 ROWS FETCH NEXT 5000 ROWS ONLY

SELECT
    A.*
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Orders
) AS A
WHERE
    A.RowNumber BETWEEN 50001 AND 55000
J. M. Arnold
  • 6,261
  • 3
  • 20
  • 38
Mihai Crivat
  • 21
  • 1
0

I found this article which presents 3 techniques of paging compares their results in a chart. Might be helpful in deciding which approach you want to follow.

Paging Function Performance in SQL Server 2012

All of the paging methods discussed work fine for smaller amounts of records, but not so much for larger quantities of data.

MMalke
  • 1,857
  • 1
  • 24
  • 35
soheil bijavar
  • 1,213
  • 2
  • 10
  • 18