DATABASE Paging using ROW_NUMBER Function IN SQL SERVER 2005

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for your database applications.
ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.

If you want the first page of 10 records from my log file sorted by dtm_DateOfJoining DESC,
you can use the ROW_NUMBER FUNCTION as follows :

SELECT Name, dtm_DateOfJoining
FROM (SELECT ROW_NUMBER() OVER (ORDER BY dtm_DateOfJoining DESC)
AS Row, vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’, dtm_DateOfJoining FROM AE_Employee)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row = 11 AND Row <= 20

output :
____________________

Amol Dhumal 1990-11-23 00:00:00.000
Akash Sokasane 1990-11-11 00:00:00.000
Swapnil Hole 1990-11-01 00:00:00.000
Aniket Kangralkar 1990-08-29 00:00:00.000
Meheraj Shaikh 1990-06-17 00:00:00.000
Pritam Wani 1990-06-12 00:00:00.000
Sunita Dhavale 1990-05-26 00:00:00.000
Umesh Jadhav 1990-05-03 00:00:00.000
Prakash Patil 1990-02-07 00:00:00.000
Priyanka Shaha 1990-02-05 00:00:00.000

Gauri Deshpande & Pawan Kumar
Pawankkmr@hotmail.com