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