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

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

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

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

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

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

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

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

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

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

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

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

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

Некоторые дополнительные чтения:

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

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

• MDSN статья в блоге: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

• Артикул: http://searchsqlserver.techtarget.com/tip/0,289483, sid87_gci1267047, 00.html#

• Неожиданное поведение и последствия для производительности временных таблиц и переменных temp: Paul White on SQLblog.com

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

Кажется в целом неверным, что существует какая-либо разница в количестве лесозаготовок (по крайней мере для 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             |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+

В каких сценариях один из них превосходит другой?

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

@wcm-на самом деле к nit pick табличная переменная не является только Ram - она может быть частично сохранена на диске.

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

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

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

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

2. Временная таблица: результат временной таблицы может быть использован несколькими пользователями.

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

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

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

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

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

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

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

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

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

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

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

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

   Переменная таблицы: в то время как переменная таблицы не будет делать этого.

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

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

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

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

Цитата взята из; Professional SQL Server 2012 Internals and Troubleshooting

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

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

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

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

Существует распространенное заблуждение, что табличные переменные являются структурами в памяти и как таковой будет работать быстрее, чем временные таблицы . Благодаря a DMV называется sys . ДМ _ дБ _ сессии _ площадь _ использования , который показывает использование данных tempdb но вы можете доказать, что это не так . После перезагрузки сервера SQL для очистки DMV, запустите следующий скрипт для подтверждения того, что ваш session _ id возвращает 0 для пользователь _ объекты _ выделено _ страница _ сосчитать :

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 ;

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

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

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

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

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

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

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

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