Anye,

Many thanks for your post.

It's news to me that the performance of a CTE query is based on the number of columns in the "Outer" query.

Rather, CTEs are generally very efficient as the processing is done with one pass of the data.

Certainly, a very quick investigation using SET STATISTICS IO does not raise any concerns.

For example, running:

SET STATISTICS IO ON

;WITH BookCTE (RowNumber, BookTitle, BookEdition, BookPublishDate, BookAuthor)

AS

(

SELECT

ROW_NUMBER()OVER (PARTITION BY BookTitle ORDER BY BookEdition DESC),

BookTitle, BookEdition, BookPublishDate, BookAuthor

FROM dbo.Books

)

SELECT BookTitle, BookEdition, BookPublishDate, BookAuthor

FROM BookCTE

WHERE RowNumber=1

ORDER BY BookTitle

...gives the following output:

Table 'Books'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Running the same query but only returning a single column (e.g. BookTitle) yields the same IO results.

See if you can find the reference that stated this behaviour, or otherwise provide a setup that would show this to be the case. Perhaps it occurs with very large tables where the processing cannot be done fully in memory(?)

I'd obviously welcome other experts' views on this point.

Best regards,

Lawrence.