While doing a code review I noticed some complex query constructions using a combination of temp variables, CTE’s and ROW_NUMBER to implement pagination in our application.
The good news was that the query worked, the better news is there is a much simpler alternative that can be used to implement pagination in your queries by using the OFFSET-FETCH clause.
An example:
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
Remark: OFFSET-FETCH can only be using in combination with an ORDER BY clause.
More information: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx