In SQL Server 2000, at a previous employer, I wrote several queries that used the same derived table several times in a single select statement. It was a royal pain to write, and when the derived table had to be modified it had to be modified in several places. Enter SQL Server 2005 and CTE's. Now, just before the select statement, I can define to "derived table" once and use it several times, just like a table in the select statement. If the CTE needs to be modified, one place to make the change.

CTE's are very useful when used properly. Sometimes a temporary table is a better option in a stored proc.