В чем разница между временной таблицей и табличной переменной в SQL Server?

@wcm - на самом деле, чтобы придраться к переменной таблицы, это не только Ram - ее можно частично сохранить на диске.

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

Хорошая справочная статья

Для всех, кто верит в миф о том, что временные переменные находятся только в памяти

Во-первых, переменная таблицы НЕ обязательно находится в памяти. При нехватке памяти страницы, принадлежащие табличной переменной, могут быть перенесены в базу данных tempdb.

Прочтите статью здесь: TempDB :: переменная таблицы vs локальная временная таблица

In which scenarios does one out-perform the other?

Для небольших таблиц (менее 1000 строк) используйте временную переменную, в противном случае используйте временную таблицу.

Между временными таблицами (#tmp) и табличными переменными (@tmp) есть несколько различий, хотя использование tempdb не входит в их число, как указано в ссылке MSDN ниже.

Как правило, для небольших и средних объемов данных и простых сценариев использования следует использовать табличные переменные. (Это слишком широкое руководство с, конечно, множеством исключений - см. Ниже и следующие статьи.)

Некоторые моменты, которые следует учитывать при выборе между ними:

• Временные таблицы - это реальные таблицы, поэтому вы можете делать такие вещи, как CREATE INDEXes и т. д. Если у вас есть большие объемы данных, доступ к которым по индексу будет быстрее, временные таблицы - хороший вариант.

• Табличные переменные могут иметь индексы с использованием ограничений PRIMARY KEY или UNIQUE. (Если вы хотите, чтобы неуникальный индекс просто включал столбец первичного ключа в качестве последнего столбца в ограничении уникальности. Если у вас нет уникального столбца, вы можете использовать столбец идентификатора.) SQL 2014 также имеет неуникальные индексы.

• Табличные переменные не участвуют в транзакциях, а SELECT неявно связаны с NOLOCK. Поведение транзакции может быть очень полезным, например, если вы хотите выполнить ОТКАТ на полпути через процедуру, тогда переменные таблицы, заполненные во время этой транзакции, все равно будут заполнены!

• Временные таблицы могут часто приводить к перекомпиляции хранимых процедур. Табличных переменных не будет.

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

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

• Использование табличных переменных в пользовательских функциях позволяет использовать эти функции более широко (подробности см. В документации CREATE FUNCTION). Если вы пишете функцию, вы должны использовать табличные переменные вместо временных таблиц, если нет особой необходимости в ином.

• Табличные переменные и временные таблицы хранятся в tempdb. Но для табличных переменных (с 2005 года) по умолчанию используется сопоставление текущей базы данных по сравнению с временными таблицами, которые принимают сопоставление по умолчанию tempdb (ссылка). Это означает, что вы должны знать о проблемах сопоставления, если вы используете временные таблицы, и ваша сортировка db отличается от tempdb, что вызывает проблемы, если вы хотите сравнить данные во временной таблице с данными в вашей базе данных.

• Глобальные временные таблицы (## tmp) - это еще один тип временных таблиц, доступных для всех сеансов и пользователей.

Дальнейшее чтение:

• Отличный ответ Мартина Смита на dba.stackexchange.com

• Часто задаваемые вопросы MSDN о различиях между ними: https://support.microsoft.com/en-gb/kb/305977

• Статья в блоге MDSN: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local- Contemporary-table

• Артикул: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• Неожиданное поведение и влияние на производительность временных таблиц и временных переменных: Пол Уайт на SQLblog.com

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

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

Еще одно отличие:

К таблице var можно получить доступ только из операторов внутри процедуры, которая ее создает, но не из других процедур, вызываемых этой процедурой или вложенным динамическим SQL (через exec или sp_executesql).

С другой стороны, область действия временной таблицы включает код в вызываемых процедурах и вложенный динамический SQL.

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

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

В целом кажется неправдой, что есть какая-либо разница в количестве журналов (по крайней мере, для операций insert / update / delete с самой таблицей, хотя у меня есть с тех пор как был найден, что есть небольшая разница в этом отношении для кешированных временных объектов в хранимых процедурах из-за дополнительных обновления системной таблицы).

Я посмотрел на поведение журналов в таблицах @table_variable и #temp для следующих операций.

1. Успешная вставка
2. Многострочная вставка, где оператор откатился из-за нарушения ограничения.
3. Обновлять
4. Удалить
5. Освободить

Записи журнала транзакций были практически идентичны для всех операций.

Версия табличной переменной на самом деле имеет несколько записей журнала дополнительный, потому что она получает запись, добавленную (а затем удаляемую) в базовую таблицу sys.syssingleobjrefs, но в целом было зарегистрировано на несколько байтов просто, поскольку внутреннее имя для табличных переменных потребляет на 236 байтов меньше, чем для Таблицы #temp (на 118 символов меньше nvarchar).

Полный сценарий для воспроизведения (лучше всего запускать на экземпляре, запущенном в однопользовательском режиме и использующем режим sqlcmd)

:setvar tablename "@T" 
:setvar tablescript "DECLARE @T TABLE"

/*
 --Uncomment this section to test a #temp table
:setvar tablename "#T" 
:setvar tablescript "CREATE TABLE #T"
*/

USE tempdb 
GO    
CHECKPOINT

DECLARE @LSN NVARCHAR(25)

SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null) 


EXEC(N'BEGIN TRAN StartBatch
SAVE TRAN StartBatch
COMMIT

$(tablescript)
(
[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,
InRowFiller char(7000) DEFAULT ''A'',
OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)
)


BEGIN TRAN InsertFirstRow
SAVE TRAN InsertFirstRow
COMMIT

INSERT INTO $(tablename)
DEFAULT VALUES

BEGIN TRAN Insert9Rows
SAVE TRAN Insert9Rows
COMMIT


INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns

BEGIN TRAN InsertFailure
SAVE TRAN InsertFailure
COMMIT


/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/
BEGIN TRY
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20
FROM sys.all_columns
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH

BEGIN TRAN Update10Rows
SAVE TRAN Update10Rows
COMMIT

UPDATE $(tablename)
SET InRowFiller = LOWER(InRowFiller),
    OffRowFiller  =LOWER(OffRowFiller),
    LOBFiller  =LOWER(LOBFiller)


BEGIN TRAN Delete10Rows
SAVE TRAN Delete10Rows
COMMIT

DELETE FROM  $(tablename)
BEGIN TRAN AfterDelete
SAVE TRAN AfterDelete
COMMIT

BEGIN TRAN EndBatch
SAVE TRAN EndBatch
COMMIT')


DECLARE @LSN_HEX NVARCHAR(25) = 
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)        

SELECT 
    [Operation],
    [Context],
    [AllocUnitName],
    [Transaction Name],
    [Description]
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  

SELECT CASE
         WHEN GROUPING(Operation) = 1 THEN 'Total'
         ELSE Operation
       END AS Operation,
       Context,
       AllocUnitName,
       COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
       COUNT(*)                              AS Cnt
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  
GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())

Полученные результаты

+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
|                       |                    |                           |             @TV      |             #TV      |                  |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  |
| LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  |
| LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  |
| LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  |
| LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  |
| LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  |
| LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  |
| LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              |
| LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  |
| LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  |
| LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  |
| LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  |
| LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  |
| LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  |
| LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  |
| LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  |
| LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+

1. Таблица Temp: Таблица Temp проста в создании и резервном копировании данных.

   Табличная переменная: Но табличная переменная требует усилий, когда мы обычно создаем обычные таблицы.

2. Таблица Temp: Результат таблицы Temp может использоваться несколькими пользователями.

   Табличная переменная: Но табличная переменная может использоваться только текущим пользователем.

3. Временная таблица: временная таблица будет храниться в базе данных tempdb. Это сделает сетевой трафик. Когда у нас есть большие данные во временной таблице, они должны работать в базе данных. Проблема с производительностью будет существовать.

   Табличная переменная: но табличная переменная будет храниться в физической памяти для некоторых данных, а затем, когда размер увеличится, она будет перемещена в базу данных tempdb.

4. Временная таблица: временная таблица может выполнять все операции DDL. Это позволяет создавать индексы, удалять, изменять и т. д.,

   Табличная переменная: Табличная переменная не позволяет выполнять операции DDL. Но табличная переменная позволяет нам создавать только кластерный индекс.

5. Таблица Temp: Таблица Temp может использоваться для текущего сеанса или глобального. Так что многопользовательский сеанс может использовать результаты в таблице.

   Табличная переменная: Но табличная переменная может использоваться до этой программы. (Хранимая процедура)

6. Временная таблица: временная переменная не может использовать транзакции. Когда мы выполняем операции DML с временной таблицей, это может быть откат или фиксация транзакций.

   Табличная переменная: Но мы не можем сделать это для табличной переменной.

7. Временная таблица: функции не могут использовать временную переменную. Более того, мы не можем выполнять операцию DML в функциях.

   Табличная переменная: но функция позволяет нам использовать табличную переменную. Но с помощью табличной переменной мы можем это сделать.

8. Таблица Temp: хранимая процедура выполнит перекомпиляцию (не может использовать тот же план выполнения), когда мы будем использовать временную переменную для каждого последующего вызова.

   Переменная таблицы: В то время как переменная таблицы так не поступает.

Цитата взята из; Внутреннее устройство Professional SQL Server 2012 и устранение неполадок

Statistics The major difference between temp tables and table variables is that statistics are not created on table variables. This has two major consequences, the fi rst of which is that the Query Optimizer uses a fi xed estimation for the number of rows in a table variable irrespective of the data it contains. Moreover, adding or removing data doesn’t change the estimation.

Indexes You can’t create indexes on table variables although you can create constraints. This means that by creating primary keys or unique constraints, you can have indexes (as these are created to support constraints) on table variables. Even if you have constraints, and therefore indexes that will have statistics, the indexes will not be used when the query is compiled because they won’t exist at compile time, nor will they cause recompilations.

Schema Modifications Schema modifications are possible on temporary tables but not on table variables. Although schema modifi cations are possible on temporary tables, avoid using them because they cause recompilations of statements that use the tables.

ТАБЛИЧНЫЕ ПЕРЕМЕННЫЕ НЕ СОЗДАЮТСЯ В ПАМЯТИ

Существует распространенное заблуждение, что переменные таблицы являются структурами в памяти. и как таковые будут работать быстрее, чем временные таблицы. Благодаря DMV называется sys. dm _ db _ session _ space _ usage, который показывает использование tempdb сеанс, вы можете доказать, что это не так. После перезапуска SQL Server, чтобы очистить DMV, запустите следующий сценарий, чтобы убедиться, что ваш идентификатор сеанса возвращает 0 для user _ objects _ alloc _ page _ count:

SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Теперь вы можете проверить, сколько места занимает временная таблица, запустив следующую команду: скрипт для создания временной таблицы с одним столбцом и заполнения ее одной строкой:

CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Результаты на моем сервере показывают, что для таблицы была выделена одна страница в базе данных tempdb. Теперь запустите тот же скрипт, но используйте табличную переменную. в это время:

DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Какой использовать?

Whether or not you use temporary tables or table variables should be decided by thorough testing, but it’s best to lean towards temporarytables as the default because there are far fewer things that can gowrong.

I’ve seen customers develop code using table variables because they were dealing with a small amount of rows, and it was quicker than a temporary table, but a few years later there were hundreds of thousands of rows in the table variable and performance was terrible, so try and allow for some capacity planning when you make your decision!

Различия между Temporary Tables (##temp/#temp) и Table Variables (@table) заключаются в следующем:

1. Table variable (@table) создан в memory. В то время как Temporary table (##temp/#temp) создается в tempdb database. Однако при нехватке памяти страницы, принадлежащие табличной переменной, могут быть помещены в базу данных tempdb.

2. Table variables не может быть задействован в transactions, logging or locking. Это делает @table faster then #temp. Таким образом, табличная переменная работает быстрее временной таблицы.

3. Temporary table позволяет изменять схему, в отличие от Table variables.

4. Temporary tables видны в созданной подпрограмме, а также в дочерних подпрограммах. Принимая во внимание, что переменные таблицы видны только в созданной подпрограмме.

5. Temporary tables разрешен CREATE INDEXes, тогда как Table variables не разрешен CREATE INDEX, вместо этого они могут иметь индекс с помощью Primary Key or Unique Constraint.

Меня удивляет, что никто не упомянул, что ключевое различие между ними состоит в том, что временная таблица поддерживает параллельная вставка, а табличная переменная - нет. Вы должны увидеть отличие от плана выполнения. А вот и видео с семинаров по SQL на 9 канале.

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