Fast Paging in SQL Server 2005

Paging is the mostly used in database when we need to display records page wise there are different techniques here i introduce fastest way.

Problem : We need to display records page wise with sorting on lastname and firstname columns problem is that our selection table is very large and mostley people used query like

Declare @PageIndex INT, @PageSize INT

WITH PagingRecord AS (
SELECT UserId,ROW_NUMBER() OVER (ORDER BY LastName,FirstName)AS Row, LastName,FirstName
FROM Users)

SELECT *
FROM PagingRecord
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

problem in this query if there are large amount of records according to above query first we load all records and order by lastname and firstname the query take so much time to execute.

My point is that why we load all records every time when we move to next page.

Solution : We need to load records according to page index and page size for example Pageindex = 2 ,Pagesize = 50 we need to load only 100 records instead of all records and out of those 100 records display 51 to 100 records.
For this use below query


Create PROCEDURE dbo.GetRecordsByPaging
@PageIndex INT,
@PageSize INT
AS
BEGIN
declare @N int
set @N = @PageIndex * @PageSize;
WITH PagingRecord AS (
SELECT top (@N) UserID,ROW_NUMBER() OVER (ORDER BY LastName,FirstName)AS Row, LastName,FirstName
FROM Users
)
SELECT *
FROM PagingRecord
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END



Exec GetRecordsByPaging 2,50
return 2nd page records from 51 to 100.