A CTE o no a CTE

Personalmente, una vez que me sentí cómodo usándolos, creo que producen un código más limpio y legible. Como ejemplo, compare su respuesta con la mía en #4018793. Esencialmente hicimos lo mismo; usé un CTE y tú no.

Su respuesta sin CTE:

SELECT
    course,
    section,
    grade,
    gradeCount
FROM
    table
INNER JOIN
    (SELECT
        grade,
        Max(gradeCount) as MaxGradeCount
    FROM
        table
    ) MaxGrades
    ON  table.grade = MaxGrades.grade
        AND table.gradeCount = MaxGrades.MaxGradeCount
ORDER BY 
    table.grade

Mi respuesta con CTE:

;with cteMaxGradeCount as (
    select 
        grade, 
        max(gradeCount) as MaxGradeCount
    from @Test
    group by grade
)
select 
    t.course, 
    t.SECTION, 
    t.grade, 
    t.gradeCount
from cteMaxGradeCount c
inner join @Test t
    on  c.grade = t.grade
        and c.MaxGradeCount = t.gradeCount
order by t.grade

Son azúcar sintáctica, con la excepción de las consultas jerárquicas/recursivas.

Sin embargo, no todo lo que se puede hacer recursivamente debería ser - la generación de fechas a través de CTE recursivo era apenas mejor que un cursor the el truco de la tabla de NÚMEROS escaló mucho mejor.

CTE produce un resultado más rápido en un escenario recursivo. El resultado de CTE se utiliza repetidamente para obtener el resultset final. Así que ya que ha tomado su cláusula where o subconsulta en CTE, definitivamente va a mostrar una mejora en el rendimiento.
Referencia: http://msdn.microsoft.com/en-us/library/ms190766 (v = sql.105).aspx

Solo una nota, en muchos escenarios, las tablas temporales también dan un mejor rendimiento que CTE, por lo que también debe probar las tablas temporales.
Referencia : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb