SQL Server : OFFSET FETCH performs scan while TOP WHERE performs seek?

Question

I got the following two queries. One is fast the other is slow.

The table has a clusted index on the Id column.

-- Slow, uses clustered index scan reading 100100 rows
SELECT * 
FROM [dbo].[Foo] 
ORDER BY Id
OFFSET 100000 ROWS FETCH FIRST 100 ROWS ONLY

-- Fast, uses clustered index seek reading 100 rows
SELECT TOP 100 * 
FROM [dbo].[Foo]
WHERE Id > 100000
ORDER BY Id

The plans are identical except for one uses a scan the other a seek.

Can anyone explain why or is this simply how OFFSET works?

The table is very wide with a few NVARCHAR(100-200) and a single NVARCHAR(2500) column.

Answer 1

The two queries are not equivalent. Although you might assume that the ids have no gaps and start at 1, the database engine does not know that.

Indexes are organized to find particular values quickly. They generally do this by traversing a tree structure, and one which is generally balanced. You can read more about this in the documentation.

However, they are not organized to quickly get to the nth row in the table. Hence, the query needs to scan the table to count the number of rows.

That said, the index could do what you want if it kept the number of rows in each child. Do realize that this would complicate modifications to the table, because the entire hierarchy would need to be updated for each update, insert, and delete.