Сравнение временных таблиц, табличных переменных и обобщенных табличных выражений (CTE)

Последнее время стали часто обсуждать временные таблицы, табличные переменные и cte. По этой причине было принято решение вынести это обсуждение в отбельную статью.

Временные таблицы. Производительность (temporal tables)

Существует несколько основных моментов, на которые стоит обратить внимание:

1. Перемещение данных во временную таблицу может вызвать большую нагрузку на дисковую подсистему, где она лежит tempdb.
2. SQL Server очень плотно работает с tempdb и бывает сложно гарантировать время выполнения запросов, которые активно её используют, так как может быть существенная конкуренция за эту БД

Большим преимуществом временных таблиц является то, что на них можно создавать индексы и статистику. Это может существенно ускорить выполнение запросов.

Табличные переменные. Производительность

Самое большие заблуждение, связанное с табличными переменными это то, что многие полагают, что они всегда располагаются в памяти, но это не так. Табличные переменные, как и временные таблицы в последних версиях SQL Server, располагаются в памяти до того момента, пока размер выборки не станет слишком большим, после чего он непременно будет сброшен в tempdb. Если памяти недостаточно или SQL Server испытывает давление на память, то сброс в tempdb происходит чаще.

На что стоит обратить внимание:

1. Табличные переменные не позволяют выполнять DDL операции, поэтому вы не можете создать индексы, для улучшения выполнения запросов. Создание UNIQUE constraint позволяет обойти это ограничение.
2. Если вопросы с индексами решить как-то возможно, то отсутствие статистики на табличных переменных никак не побороть. На средних и больших выборка это приведёт к проблемам с производительностью.

Создавайте табличные переменные только на малом объёме данных, где нет необходимости в индексах и статистике. Никогда не пользуйтесь табличными переменными, если выборка может содержать более 1000 строк. Я рекомендую не пользоваться табличными переменными уже начиная с 50-100 строк.

Обобщённое табличное выражение. Производительность (CTE)

CTE — это на самом деле только синтаксический способ разбить запрос, который работает в рамках одного запроса. Внутри SQL Server это похоже на создание VIEW «на лету», к которому можно обратиться несколько раз в рамках одного запроса. Вот когда Microsoft рекомендует использовать CTE:

1. Создания рекурсивных запросов. Дополнительные сведения см. в разделе Рекурсивные запросы, использующие обобщенные табличные выражения.
2. Замены представлений в тех случаях, когда использование представления не оправдано, то есть тогда, когда нет необходимости сохранять в метаданных базы его определение.
3. Группирования по столбцу, производного от скалярного подзапроса выборки или функции, которая недетерминирована или имеет внешний доступ.
4. Многократных ссылок на результирующую таблицу из одной и той же инструкции.

Таблицы сравнения