Steve Jones explains how the OFFSET-FETCH process works:
The other day I saw an article on the OFFSET clause in a SELECT. I had seen this come out and looked at it briefly in SQL Server 2012, but hadn’t done much with it.
NOTE: if you use this, be sure you read about potential performance problems and solutions.
Read on for more info. This is something I really wanted to work better than it does, as paging is really awkward in SQL Server. OFFSET-FETCH is great syntactically, but doesn’t do much for performance. The best solution I’ve seen is to take the results of a paging operation and store them in a dedicated paging table, allowing the user to query by page in that small table quickly. But at that point, OFFSET-FETCH isn’t really much less complicated than TOP(NumberOfRows) WHERE NumberOfRows >= NumberOfRows * (NumberOfPages – 1) ORDER BY NumberOfRows.