К CTE или не к CTE

Задерживаясь с SQL2000 слишком долго, у меня не было большой подверженности общим табличным выражениям.

Ответы, которые я дал здесь (# 4025380) и здесь (# 4018793), прошли против потока в том, что они не использовали CTE.

Я ценю, что для рекурсии они являются beez kneez, и есть несколько запросов, которые могут быть значительно упрощены их использованием, но в какой момент их использование просто необоснованно? У них отличная производительность при подзапросе или объединении? Действительно ли они упрощают код и делают его более удобным?

Короче говоря, когда рекомендуется использовать CTE над синтаксисом "lesser".

Ответ 1

Обычно вы должны использовать CTE над обычным подзапросом, если:

Ваш запрос требует рекурсии (как вы отметили)
Подзапрос большой или сложный
содержащий запрос большой или сложный
Подзапрос повторяется (или, по крайней мере, несколько подзапросов могут быть упрощены путем выполнения различных простых операций в общем подзапросе)

Короче говоря, да, они делают запросы более читабельными, когда они хорошо используются.

Ответ 2

Лично, как только мне стало удобно пользоваться ими, я думаю, что они производят более чистый и читаемый код. В качестве примера сравните свой ответ с моим на # 4018793. Мы по существу сделали то же самое; Я использовал CTE, и вы этого не сделали.

Ваш ответ без 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

Мой ответ с 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

Ответ 3

Это синтаксический сахар, за исключением иерархических/рекурсивных запросов.

Однако не все, что может быть сделано рекурсивно, должно быть - генерация даты через рекурсивный CTE была едва лучше, чем курсор. Трюк таблицы NUMBERS значительно улучшился.

Ответ 4

CTE дает более быстрый результат в рекурсивном сценарии. Результат CTE неоднократно используется для получения окончательного набора результатов. Поэтому, поскольку вы приняли предложение where или подзапрос в CTE, определенно это покажет улучшение производительности.
Ссылка: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Просто заметьте, во многих сценариях временные таблицы дают более высокую производительность, чем CTE, поэтому вы также должны попытаться создать таблицы temp. Ссылка: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb