Временные переменные, таблицы

Довольно часто приходится слышать то, что различие между табличными переменными и временными таблицами заключается в том, что первые всегда хранятся в памяти, а последние в 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, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц". Кроме того, отмечено, что сценарии использования могут быть разными, и в каждом конкретном случае нужно смотреть и пробовать что вам больше подходит.