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

CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE
ON TRANS_TABLE(MYDATE)
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID
ON TRANS_TABLE(MYGROUPID)
CTE-BasedPaging
New to SQL Server 2005 is the ranking ROW_NUMBER function, providing functionality available
to ORACLE users for years. The old way to page and sort in ORACLE was to apply the ROWNUM()
function to a resultset and get all the results less than the page you were interested in, then filter out the
rows starting with the first row on the page. In SQL Server 2005, you can write something similar to page
through the TRANS_TABLE data that would look like this:
DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int
SELECT @START_ROW = 1, @MAX_ROWS = 25
select *
from ( select p.*, rownum rnum
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
)p
where rownum < = @START_ROW + @MAX_ROWS - 1
)z
where rnum > = @START_ROW
SQL Server 2005 also introduced the idea of Common Table functions, allowing for an easier way of
coding the same thing. The CTE simply takes the place of the two derived tables, leaving a syntactically
easier query to code.
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
)
SELECT *
FROM PAGED
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
Not only is this more easily implemented, the performance and execution plans are the same (except for
derived table definition). A sample of the two plans is not shown here for brevity, but can be downloaded
at www.wrox.com in the code for this chapter.
You'll also find that this CTE method is the most commonly found recommendation for paging and
sorting. There are other options to perform the same task, but before we examine them, there are some
performance gains to add to the typical paging CTE. Note in the CTE example that all columns are being
returned in the CTE. The more scalable way to arrange this is to return only the RowNum value and a
lookup key to the TRANS_TABLE like this:
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;