Hugo Kornelis (12/6/2011)
(For some reason I can't really explain, I am not very fond of the derived table version - I've used derived tables when needed, but always found the syntax to be lacking readability).
I'm somewhere in the middle on this one. I'd like to get you and Adam Machanic together in a room on this point though - he's a fierce advocate of the benefits of derived tables over CTEs. Something about the ability to select blocks of code to run easily I think is one main argument in favour.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi