Performance issue using new paging stynax in SQL Server 2012 (OFFSET n ROWS FETCH NEXT m ROWS ONLY)

Question

I found a post about new paging stynax in SQL Server 2012. Like

SELECT p.ProductName
FROM Products p
ORDER BY p.ProductID

OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

That was great. But when I look into Execution Plan, the cost is 100%. Does it mean the SELECT statement will fetch all rows and then do the paing? which will cause a performance issue?

I am new to SQL Server, can anyone tell me about this? thanks.

Answer 1

No cost means what is the cost of a particular statement compared to all the statements. Since you have only one statement, it is taking 100% of the whole execution time. If you had two statements, then it would have split the cost.

Also to avoid confusion, I suggest you write the query this way which makes it clear that the OFFSET is actually part of the SELECT query:

SELECT p.ProductName
FROM Products p
ORDER BY p.ProductID
   OFFSET 10 ROWS
   FETCH NEXT 10 ROWS ONLY

Helps?