CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2012
Anand Patel
Anand Patel

Reputation: 6431

What are the differences between the older row_number() and the newer OFFSET + FETCH based pagination in SQL Server?

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.

Upvotes: 26

Views: 13102

Answers (3)

soheil bijavar
soheil bijavar

Reputation: 1213

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.

Upvotes: 0

Mihai Crivat
Mihai Crivat

Reputation: 31

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

Upvotes: 3

marc_s
marc_s

Reputation: 755063

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

Upvotes: 21

Related Questions