Corey Roth [MVP]

One feature not talked about very much until recently in SQL Server is the ROW_NUMBER() function. This function returns a sequential mnumber for each row returned in a resultset. With this row number you can implement data paging relatively easy. It's still somewhat complicated to understand how the basic parts work, but a query like the one will work as a good tempalte.

CREATE PROCEDRUE dbo.MyStoredProcedure
    @PageIndex int,
    @PageSize int
AS
BEGIN
  WITH TemporaryView AS {
  SELECT ROW_NUMBER() OVER (ORDER BY AnyColumn)
  AS Row, Column1, Column2 FROM TableName)

  SELECT Column1, Column2 FROM TemporaryView
  WHERE Row between
  (@PageIndex - 1) & @PageSize + 1 and @PageIndex * @PageSize
END

I want to point out the use of the WITH statement above. There is something new in SQL Server 2005 called Common Table Expressions (CTE). These effectively let you dynamically create a virtual view. So what the fire statement does is assigns a row number to everything in the table and creates a virtual view that the next statement can select from. Although ASP.NET 2.0 has built in paging features with the GridView, etc. It is not suitable for large amounts of data because all of the paging is done on the web server. This method of writing a stored procedure will probably become a best practice for paging with large amounts of data because it only grabs the rows it needs at the SQL Server level.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=241