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

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

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

@wcm - фактически, чтобы выбрать переменную таблицы, это не только Ram - она ​​может быть частично сохранена на диске.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   Табличная переменная: в то время как табличная переменная не будет так делать.

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

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

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

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

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

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

Существует распространенное заблуждение, что переменные таблицы находятся в структуры памяти и как таковые будут выполняться быстрее, чем временные таблицы . Благодаря DMV под названием sys. dm _ db _ session _ space _ Использование, которое показывает использование tempdb сессией, , вы можете доказать, что это не так . После перезапуска SQL Server для очистки DMV запустите следующий сценарий, чтобы подтвердить, что ваш идентификатор сеанса возвращает 0 для пользователя _ объекты _ 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 ;

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

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

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

Различия между 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 aren’t позволил CREATE INDEX вместо этого, у них может быть индекс при помощи [1 116].