Sample Header Ad - 728x90

Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme?

16 votes
3 answers
4548 views
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. **Why are there any differences at all considering that the two forms are semantically identical and very common?** One would assume that the optimizer recognizes both and optimizes them (trivially) to the fullest. Here is a very simple case where OFFSET ... FETCH is ~2x faster according to the cost estimate. SELECT * INTO #objects FROM sys.objects SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #objects ) x WHERE r >= 30 AND r < (30 + 10) ORDER BY object_id SELECT * FROM #objects ORDER BY object_id OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY offset-fetch.png One can vary this test case by creating a CI on object_id or adding filters but it is impossible to remove all plan differences. OFFSET ... FETCH is always faster because it does less work at execution time.
Asked by usr (7390 rep)
Dec 11, 2012, 07:39 PM
Last activity: Nov 15, 2019, 09:27 AM