Server Side Paging

In the previous post I wrote about OFFSET and FETCH on SQL Denali.

Today I want to write a sample to use them in a real world:
Normally to do paging on server we do this:

USE AdventureWorks
GO
DECLARE @NumberOfRows INT = 10 -- Number of rows you want on a page
DECLARE @DesiredPageNumber INT = 5 -- Page number
SELECT *
FROM
(
SELECT RowNum = ROW_NUMBER() OVER (
ORDER BY EmployeeID), *
FROM HumanResources.Employee
) AS a
WHERE RowNum > (@NumberOfRows * (@DesiredPageNumber - 1))
AND RowNum <= (@NumberOfRows * (@DesiredPageNumber - 1)) + @NumberOfRows
ORDER BY EmployeeID

which simply by changing @DesiredPageNumber, we can display data from different pages, but using OFFSET and FETCH in SQL-#Denali make it really simpler:

USE AdventureWorks
GO
DECLARE @NumberOfRows INT = 10 -- Number of rows you want on a page
DECLARE @DesiredPageNumber INT = 5 -- Page number

SELECT *
FROM HumanResources.Employee
ORDER BY EmployeeID
OFFSET (@NumberOfRows * (@DesiredPageNumber - 1)) ROWS
FETCH NEXT @NumberOfRows ROWS ONLY;
GO

as you can see it's really simpler and makes the developer's life much easier and has better has better Performance too.




reference