Paging In SQL Server 2011 (Code Named “Denali”) , Order By Offset In SQL Server 2011, Limiting Rows In SQL Server

SQL Server 2011 (Code Name Denali) CTP (Community Technology Preview) Has been launched with exciting features and lots enhancement for SQL Developers, BI Developers and DBAs.

You Can Download It From MSDN Download Center

Let’s Start Exploring Some Exciting Features and Enhancement For SQL Developers.

Custom paging always been a challenge for developers. We generally achieve this using some logic in your web page or using RowNumber Function SQL Server or Using TempTable.

Now Paging has been come more easy with SQL Server 2011 Order By Offset Clause Lets See How ?

Create Table and Data With Following Script

Create Table Records(
Id Int Identity(1,1),
EmpName Varchar(20),
AccountNo Varchar(12)
)
Go

Insert Into Records Values
('Kuldeep Bisht','012398760987'),('Vishal Verma','987026749876'),
('Rakesh Kumar','876894326754'),('Saneep Verma','786798564329'),
('Revinder Singh','999867543987'),('Seema Singh','876549879898'),('Ashutosh','785640987567'),('Devesh Gupta','776623458123'),
('Satinder Negi','768098723456'),('Meenakshi','009812346785'),
('Manoj Pandey','767689900145'),('Sanjay Rana','980765430974')
Go

Select * from Records Order by Id
Go

Output :

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 rest of result is returned.

Select * from Records 
Order by Id 
Offset 5 Rows

Output :

So You Can See Number of Rows Specified With Offset Has Been Skipped.

Now Suppose You Want To Limit Number Rows After Offset.

Select * from Records 
Order by Id 
Offset 5 Rows 
Fetch Next 5 Rows Only

Now Let’s See How We Can Use Stored Procedure To Return Page Wise Data.

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

Above Stored Procedure Has Two Parameter

PageNumber : Record Page Number You Want To Retrieve

RecordPerPage : No Of Record To Be Returned In a Page.