Когда я должен использовать переменную таблицы против временной таблицы в sql server?

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

я написал довольно обширный ответ на сайте DBA смотреть на различия между двумя типами объектов. Это также касается вашего вопроса о диске и памяти (я не видел никакой существенной разницы в поведении между ними).

что касается вопроса в названии, хотя о том, когда использовать переменная таблицы против локальной временной таблицы у вас не всегда есть выбор. В функциях, например, можно использовать только табличную переменную, и если вам нужно записать в таблицу в дочерней области, то только a #temp таблица будет делать (возвращающие табличное значение параметры позволяют доступ только для чтения).

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

1. если вы нужен индекс, который не может быть создан неявно через UNIQUE или PRIMARY KEY ограничение тогда вам нужно #temporary таблица, так как невозможно создать их на табличных переменных. (Примерами таких индексов являются неуникальные, отфильтрованные индексы или индексы с INCLUDEстолбцы D). Примечание: в SQL Server 2014 г позволит неуникальных индексов, чтобы быть объявлены как inline для табличных переменных.
2. если вы будете повторно добавлять и удалять большое количество строк из таблицы, то используйте #temporary стол. Это поддерживает TRUNCATE (что более эффективно, чем DELETE для больших таблиц) и дополнительно последующие вставки после TRUNCATE может иметь лучшую производительность, чем те, которые следуют за DELETEкак показано здесь.
3. если вы будете удалять или обновлять большое количество строк, то временная таблица может работать намного лучше, чем переменная таблицы - если она может использовать общий доступ к наборам строк (см. "эффекты совместного использования наборов строк" ниже образец.)
4. если оптимальный план с использованием таблицы будет меняться в зависимости от данных, то используйте #temporary таблица. Это поддерживает создание статистики, которая позволяет динамически перекомпилировать план в соответствии с данными (хотя для кэшированных временных таблиц в хранимых процедурах перекомпиляции поведения нужно разбираться отдельно).
5. если оптимальный план запроса с использованием таблицы вряд ли когда-либо изменится, вы можете рассмотреть таблицу переменная, чтобы пропустить накладные расходы на создание и перекомпиляцию статистики (возможно, потребуются подсказки для исправления нужного плана).
6. если источник данных, вставленных в таблицу, является потенциально дорогим SELECT оператор затем рассмотрим, что использование табличной переменной заблокирует возможность этого с помощью параллельного плана.
7. если вам нужны данные в таблице, чтобы пережить откат внешней пользовательской транзакции, используйте переменную таблицы. Возможный вариант использования для это может быть регистрация хода выполнения различных шагов в длинном пакете SQL.
8. при использовании #temp таблица в пользовательской транзакции блокировки могут храниться дольше, чем для переменных таблицы (потенциально до конца транзакции против Конца оператора в зависимости от типа блокировки и уровня изоляции), а также это может предотвратить усечение tempdb журнал транзакций до завершения транзакции пользователя. Таким образом, это может способствовать использованию табличных переменных.
9. сохраненные процедуры, как табличные переменные и временные таблицы могут быть кэшированы. Обслуживание метаданных для кэшированных табличных переменных меньше, чем для #temporary таблицы. Боб Уорд указывает в своем tempdb презентация что это может вызвать дополнительную конкуренцию в системных таблицах в условиях высокого параллелизма. Кроме того, при работе с небольшими объемами данных это может сделать измеримая разница в производительности.

эффекты совместного использования наборов строк

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T