Paging in SQL Server 2011 (Denali)

SQL Server 2011 has been launched with exciting features and lots enhancement for SQL developers.
You can see syntax in MSDN.

Lets start, how paging works in SQL Server 2011:

Create Table with data like so:

Now lets use Order By with Offset Clause. When you specify Order By Clause with Offset then number of rows specified with Offset are ignored and remaining records are returned.

Select * from PagingData Order By Id Offset 3 rows

Output like so:

So you can see number of rows specified with Offset has been skipped.

Note: SQL will throw error if Order By is not used in query.

Now Limit the numbers of rows after Offset like

Select * from PagingData Order By Id Offset 3 Rows Fetch next 3 rows only

Now Let’s see, How We can use Stored Procedure to return Page Wise Data.

  Create Procedure Usp_GetPageWisePagingData  
    (  
    @PageNumber Int,  
    @RecordPerPage Int  
    )  
    AS  
    Begin  
    Select * From PagingData  
    Order By Id  
    Offset ((@PageNumber-1)*@RecordPerPage) Rows  
    Fetch Next @RecordPerPage Rows Only  
    End  
    Go   

Accordingly Passed two parameters named PageNumber (which is record page number), and RecordPerPage (which is number of record) in stored procdure, we will get the result paging wise.