Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 305

The conclusion is that the higher the number of rows you need to page through, the worse performance is
going to get with the CTE. This is undeniably the case when all the columns are in the CTE, but the CTE
that brings back just the key columns is still hovering around the 1-second mark for a million paged rows,
so this may still be considered acceptable. This leads to the first and second rules of paging and sorting:
keep the resultset as small as possible by controlling the WHERE predicate, and keep the columns as
narrow as possible. The cost should be incurred seeking the correct page, not in retrieving, especially
since you are retrieving only a page worth, typically 25 to 100 rows. In terms of optimization, this is an
acceptable trade-off. Typically, a user will page through 1 to 5 pages and then change the search criteria,
so having the code scale into the 39,000th and 40,000th page is not critical.
ROW_COUNT-BasedPaging
Another historical option for paging is using the ROW_COUNT function to throttle the number of rows
that can be returned by any statement. The goal in this method (which has been around since the SQL
Server 7.0 days) is to retrieve the last identifying key into variables in one step for the starting point. Then
the ROW_COUNT function is used again to restrict the resultset after querying from the starting point.
An example of this method looks like this:
DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int,
@START_DATETIME DATETIME, @TOT_ROW_CNT INT
SELECT @START_ROW = 1, @MAX_ROWS = 25
-- Get the first row for the page
SET ROWCOUNT @START_ROW
SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDATE, MYID
-- Now, set the row count to MaximumRows and get
-- all records > = @first_id
SET ROWCOUNT @MAX_ROWS
SELECT *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID > = @START_ROW
AND MYDATE > = @START_DATETIME
ORDER BY MYDATE, MYID
SET ROWCOUNT 0
Logically, this option looks good. One quick seek for an entity key, and then pull only the rows that
you need. Outside of making sure nothing goes wrong between the SET ROWCOUNT statements
to return the ROWCOUNT state to the default, the main issue with this method is that it only excels
when the sort is against the clustered index. Typically, sort and paging procedures don't just sort against
the non-clustered index, but almost any column in the resultset. Table 9-28 compares this method
to the tuned CTE.
You may have noticed that the CTE and ROW_COUNT examples use a non-clustered index for the
sort (MYDATE). This is on purpose, to give the ROW_COUNT not too good of a show. The results
presented in Table 9-29 show a dramatic difference in the page reads and speed of the CTE against the
ROW_COUNT method. However, the ROW_COUNT method is a bit faster if the sort on MYDATE
is removed.