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

Table 9-28: Non-Clustered Sort Paging Metrics

Method % Cost CPU Reads Duration

First Page

ROW_COUNT 99 4136 6365 7221ms

CTE 1 30 82 121ms

Last Page

ROW_COUNT 99 801 2245 992ms

CTE 1 781 2237 899ms

Table 9-29: Clustered Sort Paging Metrics

Method % Cost CPU Reads Duration

First Page

ROW_COUNT 99 0 12 12ms

CTE 1 40 80 119ms

Last Page

ROW_COUNT 99 641 2241 658ms

CTE 1 971 3810 1033ms

On the clustered sorts, you can see a bit of an improvement that can be made, so there may be some
special scenarios where this technique can result in a lower page read requirement.

TOP@XPaging
SQL Server 2005 added the new TOP statement that can accept a row number parameter. This allows
for a similar methodology as the ROW_COUNT method without the dangers of leaving the orphaned
ROW_COUNT state on the connection. The performance of this method is very similar to the ROW_-
COUNT method except for sorting on heaps or no indexes deep into the paging schemes. Since this is an
edge-case benefit, we'll just show the query here and post the results on www.wrox.com .

DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT INT,
@START_DESC VARCHAR(10)
SELECT @START_ROW = 1, @MAX_ROWS = 25