The query paging feature was introduced in SQL Server 2012. It works by adding the OFFSET and FETCH clauses to the ORDER BY clause.

-- ==========================================================================
-- Paging Through A Result Set
--
-- The query paging feature was introduced in SQL Server 2012. It works by
-- adding the `OFFSET` and `FETCH` clauses to the "ORDER BY" clause.
--
--    IMPORTANT: Make Sure That Your Data Sorts The Same Way Each Time
--
-- Be sure to specify a deterministic set of sort columns in your "ORDER BY"
-- clause. Each `SELECT` to get the next page of results is a separate query and
-- a separate sort operation. Make sure that your data sorts the same way each
-- time. Do not leave ambiguity.
-- ============================================================================

--
-- To retrieve the first "10" rows of results:
select ProductID, Name
from Product
order by Name
offset 0 rows fetch next 10 rows only;

--
-- Changing the `OFFSET` from "0" to "8" will `FETCH` another "10" `ROWS`. The
-- `OFFSET` will skip the first "8" `ROWS`. There will be a 2-row overlap with
-- the preceding result set:
select ProductID, Name
from Product
order by Name
offset 8 rows fetch next 10 rows only;