I need to understand which one is better with respect to performance :
1. SQL 2012 has introducted FETCH OFFSET ...
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY
2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
WHERE row >= n AND row <= m
Note: there is an index of t1.Col3.