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.
Sir, It is very good tutorial.
ReplyDelete1 more thing offset and many more tag is not available in sql server, So which version all this tags are available pls give me detail.
Thanks
Chandan.
Hi Chandan
ReplyDeleteYes, There Are Many More Useful Function and Feature which are coming up with new version of sql server.
Its New Upcoming Version of SQL Server Code Named Denali Or You Can Say SQL Server 2011
Thanks Sir, From where i can download is it free or paid service.
ReplyDeleteAnd please as above tutorial always send update.
Link I have given above just check it
ReplyDeleteMSDN Download Center