Довольно часто приходится слышать то, что различие между табличными переменными и временными таблицами заключается в том, что первые всегда хранятся в памяти, а последние в tempdb. Но на самом деле это миф. И временные таблицы, и табличные переменные, создаются в tempdb (так как обязательно должно быть постоянное хранилище данных, на случай, если памяти всё же не хватит).
Убедиться в том, что табличные переменные создаются в tempdb так же, как и временные таблицы, можно проделав вот такой опыт:
SELECT COUNT(*) FROM tempdb.sys.tables;
GO
DECLARE @table_var table(id int);
SELECT COUNT(*) FROM tempdb.sys.tables;
GO
Результат выполнения 2-го COUNT(*) будет на 1 больше чем первого. Следовательно, в результате объявления табличной переменной в tempdb создалась таблица, и в способе хранения данных они не особо отличаются. Давайте теперь посмотрим на отличия табличных переменных от временных таблиц:
Преимущества:
- Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
- При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
- Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
- Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
- Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)
Недостатки:
- На табличных переменных нельзя создавать некластерные индексы
- Табличные переменные не содержат статистику
- Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
- Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса
Общие рекомендации Microsoft, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц". Кроме того, отмечено, что сценарии использования могут быть разными, и в каждом конкретном случае нужно смотреть и пробовать что вам больше подходит.
Ссылки по теме:
- Табличные переменные (MSDN)
- Временные таблицы
- FAQ по использованию табличных переменных
- Блог Kimberly Tripp
- Блог Ken Simmons
- Блог Pinal Dave
Денис Резник
LPP Soft, .Net Team Lead
Харьков, Украина