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 :)