Apart from the previous versions of SQL Server where the paging of result set is obtained using temp tables, ROW_NUMBER() and TOP, SQL Server 2012 provide simple functions for paging result sets. This comes in the form of OFFSET and FETCH.
The given example shows the tricky method used in previous version sql server.
SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY trx_id) AS row_num,
trx_id,trx_date,item_id,qty,cost
FROM trx_history
) trx WHERE trx.row_num > 20
We can achieve the same result using OFFSET and FETCH, but in more efficient way just like the example given below.
SELECT
trx_id,trx_date,item_id,qty,cost
FROM trx_history
ORDER BY trx_id ASC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
