ROW_NUMBER Function--Paging Records Using SQL Server 2005 Database

ROW_NUMBER returns a sequential number for each row returned in a resultset, starting from 1. It can help with paging records for the database applications.
Let see the Example:


Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers


If you have lots of records on your database and you want certain number of records to retrieve (at a time) from database to make it faster and efficient, then this is the best idea.


Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers where row>=1 and row<=50

Let say you have 100 thousands records and you made a interface to access those records. If you try to get all the records at a time, it might crash the internet explorer. In this situation, you can write stored procedure, that would accept 'startRowIndex' and 'MaximumRows' (where you define the maximum rows to display in a page). In the mean time, if you display those records in a Gridview control and want to do the header sorting when click on it, you can pass sortExpression parameter in your SP. I have written this SP for my database search application last week:

Create procedure RajSearch
@StartRowIndex INT,
@MaximumRows INT,
@SortExpression nvarchar(100),
@StringPass nvarchar(100)

As
declare @RajTable table
(rowId int identity(1,1), ID int,
Name nvarchar(255),
Sender nvarchar(255),
Recipient nvarchar(255),
date varchar(255),
Reel nvarchar(255),
Reference_URL nvarchar(255) )

Declare @ID int,
@Name nvarchar(255),
@Sender nvarchar(255),
@Recipient nvarchar(255),
@Date nvarchar(255),
@Reel nvarchar(255),
@Reference_URL nvarchar(255)

--Define a cursor
Declare Search cursor Fast_Forward for

with FixedList as(
SELECT ID,Name,Sender,Recipient,Date,Reel,Reference_URL,
ROW_NUMBER() OVER
(ORDER BY
(case when @sortExpression = 'Sender ASC' THEN Sender END) ASC,
(case when @sortExpression = 'Sender DESC' THEN Sender END) DESC,
(case when @sortExpression = 'Recipient ASC' THEN Recipient END) ASC,
(case when @sortExpression = 'Recipient DESC' THEN Recipient END) DESC,
(case when @SortExpression = 'Date ASC' THEN Date END) ASC,
(case when @SortExpression = 'Date DESC' THEN Date END) DESC,
(case when @sortExpression = 'Reel ASC' THEN Reel END) ASC,
(case when @sortExpression = 'Reel DESC' THEN Reel END) DESC )

AS [RowNo] from TestTable

where (Name like '%' + @stringPass + '%' or
Sender LIKE '%' + @stringPass + '%' or
Recipient LIKE '%' + @stringPass + '%' or
Date LIKE '%' + @stringPass + '%' or
Reel LIKE '%' + @stringPass + '%' or
Reference_URL LIKE '%' + @stringPass + '%'))

Select Sender, Recipient, Date, Reel, Reference_URL from FixedList
where RowNo BETWEEN @StartRowIndex AND @StartRowIndex + @MaximumRows

open Search
while 1=1
begin
fetch next from Search
into @Sender, @Recipient,@Date,@Reel,@Reference_URL

if @@FETCH_STATUS<>0
break
if(@Reel is not null and @Reference_URL is not null)
begin
--open in a new window
set @Reel = '<a href=' + '"' + @Reference_URL + '"' + ' target="_blank">' + @Reel + '</a>'
end

insert into @RajTable(ID,Name, Sender,
Recipient, Date, Reel,Reference_URL)
values
(@ID,@Name,@Sender, @Recipient, @Date@Reel,@Reference_URL)

end
close Search
deallocate Search

select Sender,Recipient,Date,Reel from @RajTable order by Date
return

Happy Programming!!