Click here to Skip to main content
15,867,453 members
Articles / Web Development / IIS
Article

Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.86/5 (23 votes)
20 Nov 20054 min read 242.3K   3.9K   86   19
With the release of SQL Server 2005, Microsoft introduces the long overdue ROW_NUMBER() function. In this article, we will walk through a C# implementation of pagination using the ROW_NUMBER() method.

Sample Image - row_number.gif

View Demo

Introduction

One of the great weaknesses of SQL Server 2000 was its inability to handle data pagination well. The solution to the problem was always to select all of your results and programmatically hide the results you did not want to show. As the user clicked Next or Previous, you would again select all of the rows and only display what the user asked for. Though the desired interface is achieved through this method, it is terribly inefficient. Why couldn't we select only the data that we wanted? If you have ever had to write a search that used "like" against thousands of records, you know how terribly slow SQL Server 2000 could perform.

During my SQL Server 2000 days, I would search endlessly for a solution to this problem. I tried implementations of using a select top where my last row was greater than a parameter. This works only in some cases, like when ordering by a primary key or by date. Otherwise, this failed because of the existence of duplicate data. I also tried building stored procedures that used crazy for loops to try and accomplish this. In every instance, I would always hit a brick wall. The client would request a feature that I could not support with my method and I would always default back to the poor performance of selecting all of the rows (or many of them) and handling the paging scheme programmatically.

Throughout this process, I often theorized of a SQL Server function that could add a sequential row number to my result set and allow me to use a where clause against that row the only selects what rows I needed. After a bit of research, I found out that this function did in fact exist. The only problem was, it existed only in Oracle! I was enraged, how could something so useful be simply left out of SQL Server 2000?

A few years pass by and Microsoft releases .NET which offers a partial solution to the problem. ASP.NET offers you the ability to output-cache the results of your web control. So essentially, you can select all of the rows once and as you page through the results, pull each subsequent set from the cached results. This seems to partially solve the performance problem though you are still faced with making the initial selection. But what if you want to view live changing data? As you decrease your cache time, your performance gets worse, as you increase it, your data gets old. Eventually, you fall back on your tired old method again.

With the release of SQL Server 2005, Microsoft introduces the long overdue ROW_NUMBER() function to solve this problem. In this article, we will walk through a C# implementation of pagination using the ROW_NUMBER() method.

The Code

The first step is writing your stored procedure. The SQL code for using ROW_NUMBER() is not as intuitive as you might think. When I originally attempted to do this, I tried to simply use the ROW_NUMBER() function like I would newid(). I quickly found out, that was not going to work. After some research, I came up with the stored procedure below. Though I would have rather seen a more intuitive syntax than what is below when you think about it, it does make sense. I suppose they did not want to hide logic from the programmer and ask him to accept that something magical simply happens. In the following project, I will use a database of all zip codes in the United States.

SQL
CREATE PROCEDURE [dbo].[sp_getzipcodes] 
     -- Add the parameters for the stored procedure here
     @start int = 0

AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     Set NOCOUNT ON
     SELECT TOP 20 * FROM 
     (
          SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode
     ) AS a
     WHERE num > @start
END

Now that you have your stored procedure, you will need to display the results on a web site. In our example, we use a GridView control, but essentially this will work with any control because we set the parameter in our SQL data source like so:

ASP.NET
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:personalConnectionString %>"
     SelectCommand="sp_getzipcodes" SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:QueryStringParameter Name="start" 
           QueryStringField="start" DefaultValue="0" />
  </SelectParameters>
</asp:SqlDataSource>

Finally, you need to build your pagination controls. For this project, we accomplish this by setting a literal in our code-behind page.

C#
if ((Request.QueryString["start"] == null) | 
    (Request.QueryString["start"] == "0"))
{ 
     paging.Text = "<< prev | <a href = \"?start=20\">next >></a>";
}
else
{
     int start = Convert.ToInt32(Request.QueryString["start"]) + 1;
     int next = Convert.ToInt32(Request.QueryString["start"]) + results;
     int prev = Convert.ToInt32(Request.QueryString["start"]) - results;
     if (next > max)
     { 
          paging.Text = @"<a href = ""?start=" + prev + 
                        @"""><< prev</a> | next >></a>";
     }
     else
     { 
          paging.Text = @"<a href = ""?start=" + prev + 
                        @"""><< prev</a> | <a href = ""?start=" + 
                        next + @""">next >></a>";
     }
}

That's about it. Download the source code to get the full project. Included in the download is the Visual Studio 2005 project, SQL Server stored procedures, and the zip code database in CSV format.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United States United States
For more articles please visit my website:
http://www.aprogrammersjournal.com

A Programmers Journal
RSS

Comments and Discussions

 
GeneralNeato! Pin
SonicMouse13-Nov-07 18:58
SonicMouse13-Nov-07 18:58 
GeneralThis is great Pin
Sacha Barber21-May-07 6:14
Sacha Barber21-May-07 6:14 
GeneralRe: This is great Pin
Jason Witty13-Feb-08 15:30
Jason Witty13-Feb-08 15:30 
GeneralERROR IN ROW_NUMBER() Pin
gidy7-Apr-06 2:36
gidy7-Apr-06 2:36 
GeneralRe: ERROR IN ROW_NUMBER() Pin
Saud AKhter2-Jan-08 20:44
Saud AKhter2-Jan-08 20:44 
GeneralRe: ERROR IN ROW_NUMBER() Pin
kamal Dhillon26-Aug-08 23:41
kamal Dhillon26-Aug-08 23:41 
GeneralRe: ERROR IN ROW_NUMBER() Pin
Saud AKhter27-Aug-08 18:41
Saud AKhter27-Aug-08 18:41 
GeneralSuggestion Pin
Sergio Pereira23-Dec-05 7:12
Sergio Pereira23-Dec-05 7:12 
GeneralRe: Suggestion Pin
Jason Witty23-Dec-05 9:33
Jason Witty23-Dec-05 9:33 
GeneralRe: Suggestion Pin
Shazam9998-Jan-06 21:01
Shazam9998-Jan-06 21:01 
GeneralRe: Suggestion Pin
yordan_georgiev6-Jan-09 8:27
yordan_georgiev6-Jan-09 8:27 
GeneralRe: Suggestion Pin
Jason Witty6-Jan-09 10:42
Jason Witty6-Jan-09 10:42 
GeneralSame problem here, finally MS is growing up Pin
Kory.Postma22-Nov-05 4:23
Kory.Postma22-Nov-05 4:23 
GeneralRe: Same problem here, finally MS is growing up Pin
xyzstarr14-Mar-06 19:08
xyzstarr14-Mar-06 19:08 
GeneralSQL Performance Pin
kgbroce22-Nov-05 3:58
professionalkgbroce22-Nov-05 3:58 
GeneralRe: SQL Performance Pin
Jason Witty22-Nov-05 12:07
Jason Witty22-Nov-05 12:07 
QuestionQuestion Pin
Ashley van Gerven20-Nov-05 13:21
Ashley van Gerven20-Nov-05 13:21 
AnswerRe: Question Pin
Jason Witty20-Nov-05 15:26
Jason Witty20-Nov-05 15:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.