Custom Paging in SQL Server 2012 - Using OFFSET

In SQL Server 2005/2008, if you have want to implement the custom paging for ASP.Net GridView or any other controls, you have use either COMMON TABLE EXPRESSIONS with ROW_NUMBER() or just ROW_NUMBER() keyword.

CUSTOM PAGING IN SQL SERVER 2005/2008:

 -- CUSTOM PAGING IN SQL SERVER 2005   
 USE AdventureWorks  
 DECLARE @PageIndex SMALLINT, @PageSize SMALLINT, @StartRowId INT, @EndRowId INT  
 SET @PageIndex = 1  
 SET @PageSize = 10  
 SET @StartRowId = ( (@PageIndex - 1) * @PageSize) + 1  
 SET @EndRowId = (@StartRowId + @PageSize) - 1  
 SELECT      *  
 FROM (  
 SELECT ROW_NUMBER() OVER(ORDER BY EmployeeId) AS RowId, *  
 FROM HumanResources.Employee) AS TT  
 WHERE RowId >= @StartRowId and RowId <= @EndRowId  

CUSTOM PAGING IN SQL SERVER 2012 using OFFSET:
In SQL Server 2012, by using the OFFSET feature, we can implement the custom paging easily without having too many SQL statements.
Just you have to specify starting row id and page size like below.

 OFFSET <Offset> ROWS  
 FETCH NEXT <PageSize> ROWS ONLY;  

Here is the complete T-SQL statement for custom paging.

 -- CUSTOM PAGING IN SQL SERVER 2012 RC0  
 DECLARE @PageIndex SMALLINT, @PageSize SMALLINT, @Offset INT  
 SET @PageIndex = 1  
 SET @PageSize = 10  
 SET @Offset = ( (@PageIndex - 1) * @PageSize)  
 SELECT      *  
 FROM HumanResources.Employee  
 ORDER BY EmployeeId  
 OFFSET @Offset ROWS  
 FETCH NEXT @PageSize ROWS ONLY;