Каковы преимущества / недостатки использования CTE?

Возможно нет. CTE особенно хороши при запросе данных для древовидных структур.

Вам действительно придется пройти тест производительности - нет ответа Да / Нет. Согласно сообщению Энди Ливинга, приведенному выше, CTE - это просто сокращение для запроса или подзапроса.

Если вы вызываете его дважды или более в одной и той же функции, вы можете повысить производительность, если заполните переменную таблицы, а затем присоединитесь к ней / выберите из нее. Однако, поскольку переменные таблицы где-то занимают место и не имеют индексов / статистики (за исключением любого объявленного первичного ключа в переменной таблицы), невозможно сказать, что будет быстрее.

У них обоих есть затраты и экономия, и какой из них лучше всего зависит от данных, которые они получают, и от того, что они с ними делают. Я был в вашей ситуации и после тестирования скорости в различных условиях - некоторые функции использовали CTE, а другие использовали переменные таблицы.

Преимущества CTE

CTE можно назвать «временным представлением», используемым в некоторых случаях в качестве хорошей альтернативы представлению. Главное преимущество перед представлением - это использование памяти. Поскольку область действия CTE ограничена только его пакетом, выделенная для него память очищается, как только его пакет пересекается. Но как только представление создано, оно сохраняется до тех пор, пока пользователь не удалит его. Если представление не используется после создания, это пустая трата памяти. Стоимость ЦП для выполнения CTE меньше по сравнению с View. Как и View, CTE не хранит никаких метаданных своего определения и обеспечивает лучшую читаемость. На CTE можно ссылаться несколько раз в запросе. Поскольку область действия ограничена пакетом, несколько CTE могут иметь одно и то же имя, которое не может иметь представление. Его можно сделать рекурсивным.

Недостатки CTE

Хотя использование CTE выгодно, у него есть некоторые ограничения, о которых следует помнить. Мы знали, что это замена представления, но CTE не может быть вложенным, в то время как представления могут быть вложенными. Один раз объявленное представление можно использовать любое количество раз, но нельзя использовать CTE. Его следует объявлять каждый раз, когда вы хотите его использовать. Для этого сценария не рекомендуется использовать CTE, поскольку пользователю утомительно объявлять пакеты снова и снова. Между элементами привязки должны быть операторы типа UNION, UNION ALL или EXCEPT и т. д. В рекурсивных элементах CTE можно определить множество элементов привязки и рекурсивных элементов, но все элементы привязки должны быть определены до первого рекурсивного элемента. Вы не можете определить элемент привязки между двумя рекурсивными элементами. Количество столбцов и типы данных, используемые в Anchor и Recursive Members, должны быть одинаковыми. В рекурсивном элементе агрегатные функции, такие как TOP, оператор DISTINCT, предложения, такие как HAVING и GROUP BY, подзапросы, объединения, такие как Left Outer или Right Outer или Full Outer, не допускаются. Что касается объединений, в рекурсивном элементе разрешено только внутреннее объединение. Предел рекурсии - 32767, превышение которого приводит к сбою сервера из-за бесконечного цикла.