"If at first you don't succeed; call it version 1.0" :-Unknown

Pages

Thursday, November 7, 2013

Pagging in sql

In one of my older post i posted about  paging in sql using Row_Number function (http://simplyasp.blogspot.in/2010/11/paging-records-using-sql-server-2005.html).on latest version of sql they had improved the paging functionality by bothe performance base and also in readbility base too. on 2008  they introduce CTE (Common table expression) and Derived table.  on sql 2012 they done a mass step on that FETCH NEXT functionality ... i love that.

Below i show all the method of paging i mentioned above.

--SQL 2005/2008 Paging Method Using Derived Table
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

 
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
      FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

 
--SQL 2005/2008 Paging Method Using CTE
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;

 
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
 FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

 
--SQL SERVER 2012
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS

FETCH NEXT 50 ROWS ONLY;

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

No comments: