Зачем нужны переменные таблицы и когда их лучше использовать?

В MS SQL есть переменные таблицы.
В некоторых случаях я замечал, что темповые таблицы работают быстрее, за счет того, что в них используется параллелизм в отличии от переменных.
Единственный минус темповых таблиц, как я думаю - это то, что их нужно проверять и дропать если они есть перед создание.
Так вот, для каких целей они могут понадобится, когда есть темповые таблицы и когда их лучше использовать?

Ответ

Единственный минус темповых таблиц, как я думаю - это то, что их нужно проверять и дропать если они есть перед созданием.
На мой взгляд это проблема только при @@nestlevel равном 0, да и то не всегда (например, если открываем соединение, создаём #-таблицу, что-то делаем и закрываем соединение, то в проверке нет необходимости). Внутри модуля (триггера или процедуры), если нужно создать #table, то можно не проверять и не дропать (это может быть и вредным), даже если на внешнем уровне уже была создана #table с тем же самым именем.
Т.е., например, есть процедура
create procedure SomeProc as begin set nocount on;
create table #table (id int); insert into #table values (1), (2);
select * from #table; end GO
Можно создать #table и вызвать SomeProc
create table #table (value float); insert into #table values (100.0), (200.0); exec SomeProc; -- #table внутри SomeProc - это другая #table select * from #table; drop table #table; GO
при этом #table, созданная снаружи SomeProc, и #table, созданная внутри SomeProc - разные и между собой не интерферируют. Если внутри SomeProc перед create table #table поставить проверку существования #table и drop table #table, то уничтожится таблица, созданная на внешнем уровне, и код, следующий за процедурой, сломается.
Так вот, для каких целей они могут понадобиться, когда есть темповые таблицы
При выборе между @table и #table, как мне кажется, следует исходить из свойств и особенностей этих двух типов таблиц. У @-таблиц по сравнению с #-таблицами довольно много ограничений, много общего, и есть особенности присущие только @-таблицам.
Подробно этот вопрос освещён здесь и здесь. Ниже некоторое обобщение информации по этим двум ссылкам (неполное).

Хранилище: Вопреки расхожему представлению о том, что @-таблицы хранятся в оперативной памяти, а #-таблицы - в tempdb, разницы в этом плане между ними на самом деле нет. И те и другие хранятся в tempdb, и, также, и те и другие одинаково буферизуются.
Область видимости: #-таблицы доступны на том уровне, на котором они создаются и на вложенных (@@nestlevel больше текущего). @-таблицы доступны только в рамках того batch-а или модуля, в котором они объявляются (впрочем, типированные @-таблицы на вложенные уровни можно передавать параметром).
Жизненный цикл: @-таблицы создаются неявно в самом начале того батча, в котором они объявлены, перед выполнением всех остальных команд (а если это модуль, то перед началом выполнения модуля), независимо от того, будет ли достигнута в процессе исполнения соответствующая declare инструкция, или нет. Вот такой код, например, вполне работает:
if 1 != 0 declare @table1 table (id int); else declare @table2 table (name varchar(20));
select * from @table1; select * from @table2; GO
уничтожаются @-таблицы после выхода из соответствующего батча или модуля, в котором они объявлены.
#-таблицы создаются тогда, когда встречается соответствующая create table инструкция; уничтожаются тогда, когда встречается соответствующая drop table инструкция, либо при уменьшении уровня вложенности (выхода из модуля или exec инструкции), или закрытии соединения (если #-таблица была создана на нулевом уровне вложенности).
Использование в функциях и процедурах: #-таблицы нельзя создавать в пользовательских скалярных и multi-line табличных функциях, @-таблицы - можно. Типированные @-таблицы можно передавать в функции и процедуры параметром. #-таблицы нельзя передавать в функции (хотя можно получить к ним доступ косвенно - через синоним). В процедурах #-таблицы, созданные на внешнем уровне - доступны.
Сollation: При создании @-таблиц столбцы строковых типов (если они присутствуют) наследуют collation от текущей БД. При создании #-таблиц столбцы строковых типов наследуют collation от tempdb, либо от текущей БД, если та является автономной (contained).
Индексы/ключи: До SqlServer 2014 на @-таблицах можно было задавать только уникальные ключи
declare @table table ( id int primary key, uid uniqueidentifier unique )
В SqlServer 2014 с добавлением inline-синтаксиса для создания индексов стало возможным добавлять неуникальные индексы
declare @table table ( value varchar(20), index ix_1 (value) )
В SqlServer 2016 стало возможным добавление уникальных и отфильтрованных индексов
declare @table table ( id int, value varchar(20), index ix_1 unique (value), index ix_2 (id) where value is NULL )
Индексы с include, а также специальные виды индексов на @-таблицах на настоящий момент не поддерживаются.
Компиляция запросов: Оптимизатор запросов во многих случаях полагает, что @-таблица содержит одну единственную строку. На @-таблицах не поддерживаются статистики по столбцам.
Транзакции: Изменение содержимого @-таблиц всегда происходит в системной транзакции, поэтому они нечувствительны к rollback
declare @table table (value int); create table #table (value int); begin tran; insert into @table values (1); insert into #table values (2); rollback; select * from @table; select * from #table; drop table #table;
Параллелизм: Запросы, меняющие содержимое @table (insert/update/delete) не могут использовать параллелизм. Запросы читающие из @table (select) могут иметь параллельный план.
Табличные подсказки: На @-таблицах нельзя использовать табличные подсказки (table hints), на #-таблицы такое ограничение не накладывается.
Другое: @-таблицу нельзя создать при помощи select * into @table from .... Для @-таблиц недоступны truncate table, alter table, create index