Custom Paging in SQL Server

Brfore discussing custom paging, lets discusse how default paging works. Data controls like gridview are used to display data. When data is large you want to show it in parts that is possible using pagination feature of gridview. Actually gridview merely shows data, its the datasource attached to the gridview that fetchs data from the database.Now with the default paging, datasource of the gridview retrives whole data from the database and then gridview shows only the selective data from whole set of data based on the page index that user has choosen.
So, in the default paging, although you see subset of data but actually whole set of data is retrived from the DB that makes this approach preformance regressive.

Now, what is custom paging?

Through custom paging only selective set of data is retirved from the database unlike default paging. To achieve this, SQl Server 2005 has introduce a feature called ROW_NUMBER() that can be used in a query to retrive selective data from the database.

Following code example will show how to use ROW_NUMBER() in an SP that will get selective records from DB based on page index to be shown and no. of record per page parameters passed to it.

-- Create table
create table [dbo].[NUM]
(
[n] [int] NOT NULL,
[s] [varchar](128) NULL
)
GO
--Now insert 1000 records in the table.
set nocount on
declare @n int,@v int
set @n=0
while @n<1000
begin
set @v=(cast((rand()*100)as int)%100) + @n
insert into NUM
select @v,'Value: '+convert(varchar,@v)
set @n=@n+1
end
GO

-- Create stored procedure for retrieving records using sql server custom paging feature

create proc GetDataByCustomPaging
@pageindex int,
@pagesize int
as
begin
select n,s from (select ROW_NUMBER() over (order by n) as RowNum ,n,s from NUM) as tblNUM
where RowNum between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
end

-- Execute Sp by passing page index and the size of page

exec GetDataByCustomPaging 2,10

Gridview control also supports custom paging but you need to do some coding to implement it in gridview.