Sql Server 2005 brought a couple of great features to the table in regard to paging...CTE's and RowNumber(). Paging, of course, is returning a a resultset in chunks (pages) so that you don't have to return a large number of rows all at once. For example, in a web application there may be a search page that allows users to search for orders by a date range. There is always one user who wants to list every order the company has received since the beginning of time. Without paging, every row returned by the search would be returned to the web server from the database and them passed on to the browser and displayed in a mile-long web page.
It's possible to accomplish paging on the web server (much more easily in .Net 2.0 than in previous versions thanks to the inclusion of paging in several of the bundled web controls). In this case, the large result set is persisted on the web server and sent to the browser a page at a time. Using this approach, you take two sizable hits; bringing all the rows across the network from the database and storing them in memory (or possibly on disk) on the web server.
Ideally, we'd like to be able to retrieve just a page at a time from the database and display it to the user. Sql Server 2005 makes this an easy task. Here's an example.
DECLARE @PageSize INT, @PageNumber INT, @TopRow INT, @BottomRow INT
SELECT @PageSize = 15, @PageNumber = 2
SELECT @TopRow = ( @PageNumber - 1) * @PageSize + 1,
@BottomRow = @PageSize + (@PageNumber - 1) * @PageSize;
WITH Orders AS
(
SELECT Order_Number, Customer_Id, Order_Amount,
ROW_NUMBER() OVER (ORDER BY Customer_Id ASC) AS RowNumber
FROM Order_Master
)
SELECT RowNumber, Order_Number, Customer_Id, Order_Amount
FROM Orders
WHERE RowNumber BETWEEN @TopRow AND @BottomRow
ORDER BY RowNumber ASC;
In this example, we begin by declaring the variables, we'll need. Note that the page size and page number could and normally would be passed into a stored procedure containing this code. Next, we calculate the top and bottom rows of the page we're interested in displaying. These values are indexes into the resultset as a whole that represent where our page starts and where it ends. Now, we create a CTE named "Orders". I think of CTE's as temporary views. We define The CTE and then select from it. In our CTE definition, we define the select statement which is our view into the data (or in other words the select statement that would return the entire result set we're concerned with) and add a column to the statement based on the Row_Number() function. Basically, we specify a sort order and the Row_Number() function will assign a row number to each row in the resultset based on the sort order we specify. With the CTW defined, we can query it and return only the rows with row numbers that fall between the top and bottom row numbers we calculated earlier.