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

Это, кажется, область с довольно многими мифами и противоречивыми взглядами.

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

421 голос | спросил Martin Smith 12 AMpThu, 12 Apr 2012 02:22:32 +040022Thursday 2012, 02:22:32

2 ответа


631

Содержание

Содержание

Caveat

В этом ответе обсуждаются «классические» табличные переменные, представленные в SQL Server 2000. SQL Server 2014 в памяти OLTP представляет типы таблиц с оптимизацией памяти. Табличные переменные экземпляры этих во многих отношениях отличаются от тех, которые обсуждаются ниже! ( подробнее ).

Место хранения

Никакой разницы. Оба сохраняются в tempdb.

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

DECLARE @T TABLE (X INT)

INSERT INTO @T VALUES (1), (2)

SELECT sys.fn_PhysLocFormatter (%% physloc %%) AS [Файл: Страница: Slot]
FROM @T

Примеры результатов (показывающие местоположение в tempdb) сохраняются 2 строки)

Файл: Страница: Слот
----------------
(1: 148: 0)
(1: 148: 1)

Логическое местоположение

@table_variables ведут себя так же, как если бы они были частью текущей базы данных, чем таблицы #temp. Для переменных таблицы (с 2005 года) сопоставление столбцов, если явно не указано, будет иметь значение текущей текущей базы данных, тогда как для таблиц #temp будет использоваться сортировка по умолчанию tempdb ( Подробнее ). Кроме того, пользовательские типы данных и коллекции XML должны быть в tempdb для использования в таблицах #temp, но переменные таблицы могут использовать их из текущей базы данных ( Источник ).

SQL Server 2012 представляет содержащиеся базы данных. поведение временных таблиц в этих переменных (h /t Aaron)

  

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

     
  • Все метаданные, связанные с временными таблицами (например, имена таблиц и столбцов, индексы и т. д.), будут находиться в сортировке каталогов.
  •   
  • Именованные ограничения не могут использоваться во временных таблицах.
  •   
  • Временные таблицы могут не относиться к пользовательским типам, коллекциям XML-схем или определенным пользователем функциям.
  •   

Видимость для разных областей

@table_variables доступен только в пределах партии и области, в которой они объявлены. #temp_tables доступны в дочерних пакетах (вложенные триггеры, процедуры, exec)). #temp_tables, созданный во внешней области (@@ NESTLEVEL = 0), может также охватывать партии, поскольку они сохраняются до окончания сеанса. Ни один из типов объектов не может быть создан в дочерней партии и доступен в области вызова, однако, как обсуждалось ниже (глобальные ## temp таблицы могут ), хотя).

Lifetime

@table_variables создаются неявно, когда выполняется пакет, содержащий инструкцию DECLARE @ .. TABLE (до того, как какой-либо код пользователя в этой партии запущен) и неявно отбрасывается в конец.

Хотя анализатор не позволит вам попробовать использовать переменную таблицы перед оператором DECLARE, неявное создание может быть показано ниже.

IF (1 = 0)
НАЧАТЬ
DECLARE @T TABLE (X INT)
КОНЕЦ

--Работает отлично
ВЫБРАТЬ *
FROM @T

#temp_tables создаются явно, когда оператор TSQL CREATE TABLE встречается и может быть явно опущен с помощью DROP TABLE) или будет отброшен неявно когда пакет заканчивается (если он создан в дочерней партии с @@ NESTLEVEL> 0) или когда сеанс заканчивается иначе.

NB: внутри хранимых процедур оба типа объектов могут быть кэшированы вместо многократного создания и удаления новых таблиц. Существуют ограничения на то, когда это кеширование может произойти, но которые могут быть нарушены для #temp_tables, но ограничения на @table_variables в любом случае предотвращают. Накладные расходы на обслуживание для кэшированных таблиц #temp меньше больше, чем для переменных таблицы , как показано здесь .

Метаданные объекта

Это по сути то же самое для обоих типов объектов. Он хранится в базовых таблицах системы в tempdb. Более просто видеть таблицу #temp, однако, какOBJECT_ID ('tempdb .. # T') может использоваться для ввода в системные таблицы, а внутренне сгенерированное имя более тесно связано с именем, указанным в CREATE TABLE заявление. Для переменных таблицы функция object_id не работает, и внутреннее имя полностью сгенерировано системой без отношения к имени переменной. Ниже показано, что метаданные все еще существуют, если ввести ключевое имя (надеюсь, уникальное). Для таблиц без уникальных имен столбцов object_id можно определить с помощью СТРАНИЦА DBCC , если они не пусто.

/* Объявить переменную таблицы с некоторыми необычными параметрами. * /
DECLARE @T TABLE
(
[dba.se] INT ИДЕНТИФИКАЦИЯ ПЕРВИЧНОГО КЛЮЧА, НЕУКАЗАННОГО,
INT CHECK (A> 0),
B INT ПО УМОЛЧАНИЮ 1,
InRowFiller char (1000) DEFAULT REPLICATE ('A', 1000),
OffRowFiller varchar (8000) DEFAULT REPLICATE ('B', 8000),
LOBFiller varchar (max) DEFAULT REPLICATE (литой ('C' как varchar (max)), 10000),
УНИКАЛЬНЫЕ КЛАСТЕРЫ (A, B)
    С (FILLFACTOR = 80,
         IGNORE_DUP_KEY = ON,
         DATA_COMPRESSION = PAGE,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ВКЛ)
)

INSERT INTO @T (A)
ЗНАЧЕНИЯ (1), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), ( 12), (13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

Выход

Дублирующий ключ игнорировался.

 + ----------- + ----------- + ------ + ------------------ - + ------------- + ------------ + ------------ + -------- --------------- +
| object_id | имя | строки | type_desc | Всего_страниц | used_pages | data_pages | data_compression_desc |
+ ----------- + ----------- + ------ + ------------------ - + ------------- + ------------ + ------------ + -------- --------------- +
| 574625090 | # 22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | СТРАНИЦА |
| 574625090 | # 22401542 | 13 | LOB_DATA | 24 | 19 | 0 | СТРАНИЦА |
| 574625090 | # 22401542 | 13 | ROW_OVERFLOW_DATA | 16 | 14 | 0 | СТРАНИЦА |
| 574625090 | # 22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | НЕТ |
+ ----------- + ----------- + ------ + ------------------ - + ------------- + ------------ + ------------ + -------- --------------- +

Сделки

Операции над @table_variables выполняются как системные транзакции, независимо от любой внешней транзакции пользователя, тогда как эквивалентные операции с таблицами #temp будут выполняться как часть самой транзакции пользователя. По этой причине команда ROLLBACK будет влиять на таблицу #temp, но не трогать @table_variable.

DECLARE @T TABLE (X INT)
CREATE TABLE #T (X INT)

НАЧАТЬ TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
ЗНАЧЕНИЯ (1), (2), (3)

/* Оба имеют 3 строки * /
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/* Только переменная таблицы теперь имеет строки * /
SELECT * FROM #T
SELECT * FROM @T
ТАБЛИЦА DROP #T

Logging

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

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

ИСПОЛЬЗОВАТЬ tempdb;

/*
Не запускайте это на занятом сервере.
В идеале не должно быть никакой параллельной деятельности вообще
* /
КОНТРОЛЬНО-ПРОПУСКНОЙ ПУНКТ;

ИДТИ

/*
Второй столбец двоичный, чтобы упростить корреляцию с выходом журнала, показанным позже * /
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY (10))

INSERT INTO @T
ЦЕННОСТИ (1, 0x41414141414141414141),
       (2, 0x41414141414141414141)

UPDATE @T
SET B = 0x42424242424242424242

УДАЛИТЬ ОТ @T

/* Поместите alloc_unit_id в CONTEXT_INFO для доступа к следующей партии * /
DECLARE @allocId BIGINT, @Context_Info VARBINARY (128)

SELECT @Context_Info = alloc_unit_id,
       @allocId = a.allocation_unit_id
FROM sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
         ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
         ON c.object_id = p.object_id
WHERE (c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3')

SET CONTEXT_INFO @Context_Info

/* Проверить журнал для записей, связанных смодификации самой переменной таблицы * /
Операция SELECT,
       Контекст,
       AllocUnitName,
       [RowLog Содержание 0],
       [Длина записи журнала]
FROM fn_dblog (NULL, NULL)
WHERE AllocUnitId = @allocId

ИДТИ

/* Проверка общего использования журналов, включая обновления по системным таблицам * /
DECLARE @allocId BIGINT = CAST (CONTEXT_INFO () AS BINARY (8));

С Т
     AS (SELECT Operation,
                Контекст,
                ДЕЛО
                  WHEN AllocUnitId = @allocId THEN 'Таблица Variable'
                  WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
                  ELSE AllocUnitName
                END AS AllocUnitName,
                [Длина записи журнала]
         FROM fn_dblog (NULL, NULL) AS D)
Операция SELECT = CASE
                     КОГДА GROUPING (Operation) = 1 THEN 'Total'
                     Операция ELSE
                   КОНЕЦ,
       Контекст,
       AllocUnitName,
       [Размер в байтах] = COALESCE (SUM ([Длина записи журнала]), 0),
       Cnt = COUNT (*)
FROM T
GROUP BY GROUPING SETS ((Операция, Контекст, AllocUnitName), ())
ORDER BY GROUPING (Операция),
          AllocUnitName

Возвращает

Подробный просмотр

Снимок экрана результатов

Сводный просмотр (включает ведение журнала для неявных таблиц и системных базовых таблиц)

Снимок экрана результатов

Насколько я смог распознать , операции на обоих генерируют примерно равное количество протоколирования.

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

Табличные переменные не поддерживают TRUNCATE, поэтому могут быть проблемы с протоколированием, когда требуется удалить все строки из таблицы (хотя для очень маленьких таблиц DELETE может получиться лучше в любом случае )

Cardinality

Многие из планов выполнения с использованием переменных таблицы показывают одну строку, оцененную как выход из них. Проверка свойств переменной таблицы показывает, что SQL Server считает, что переменная таблицы имеет нуль строки (почему она оценивает, что 1 строка будет испускаться из таблицы нулевой строки, объясняется @Paul White here ).

Однако результаты, показанные в предыдущем разделе, показывают точное число rows в sys.partitions. Проблема в том, что в большинстве случаев инструкции, ссылающиеся на переменные таблицы, скомпилируются, пока таблица пуста. Если оператор (re) скомпилирован после заполнения @table_variable, то вместо этого он будет использоваться для каркаса таблицы (это может произойти из-за явного recompile) или, возможно, из-за того, что оператор также ссылается на другой объект, который вызывает отложенный компиляцию или перекомпиляцию.)

DECLARE @T TABLE (I INT);

INSERT INTO @T VALUES (1), (2), (3), (4), (5)

CREATE TABLE #T (I INT)

/* Ссылка на #T означает, что это утверждение подлежит отсроченной компиляции * /
SELECT * FROM @T НЕ СУЩЕСТВУЕТ (SELECT * FROM #T)

ТАБЛИЦА DROP #T

В плане отображается точный оценочный подсчет строк после отложенного компиляции.

Показывает точное количество строк

В SQL Server 2012 SP2 введен флаг трассировки 2453. Более подробная информация приведена в разделе «Реляционный движок» здесь .

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

NB: на уровне Azure на уровне совместимости 150 компиляция оператора теперь отложено до первого выполнения . Это означает, что он больше не будет подвержен оценке оценки нулевой строки.

Статистика столбцов

Наличие более точной мощности таблицы не означает, что оцененный подсчет строк будет более точным (если только операция не выполняется во всех строках таблицы). SQL Server не поддерживает статистику столбцов для переменных таблицы вообще, поэтому отпадает на догадки, основанные на предикате сравнения (например, что 10% таблицы будет возвращено для = для не уникального столбца или 30% для сравнения >). В отличие от столбцов статистики поддерживаются для таблиц #temp.

SQL Server поддерживает подсчет количества изменений, внесенных в каждый столбец. Если количество модификаций с момента составления плана превышает порог перекомпиляции (RT), тогда план будет перекомпилирован и обновлена ​​статистика. RT зависит от типа и размера таблицы.

От Планирование кэширования в SQL Server 2008

  

RT рассчитывается следующим образом. (n ссылается на мощность таблицы при компиляции плана запроса.)

     

Постоянная таблица
  - Если n <= 500, RT = 500.
  - Если n> 500, RT = 500 + 0,20 * n.

     

Временная таблица
  - Если n <6, RT = 6.
  - Если 6 <= n <= 500, RT = 500.
  - Если n> 500, RT = 500 + 0,20 * n.
Переменная таблицы
  - RT не существует. Поэтому перекомпиляции не происходит из-за изменений мощностей табличных переменных.    (Но см. примечание о TF 2453 ниже)

подсказка KEEP PLAN может использоваться для установки таблиц RT для #temp так же, как и для постоянных таблиц.

Чистый эффект всего этого заключается в том, что часто планы выполнения, созданные для таблиц #temp, являются порядками величин лучше, чем для @table_variables, когда многие строки участвуют в SQL Сервер имеет лучшую информацию для работы.

NB1: переменные таблицы не имеют статистики, но могут по-прежнему подвергать событие перекомпиляции с измененной статистикой под флагом трассировки 2453 (не применяется для «тривиальных» планов). Это, похоже, происходит под теми же порогами перекомпиляции, что и для временных таблиц выше, с дополнительным, если N = 0 -> RT = 1. то есть все утверждения, скомпилированные, когда переменная таблицы пуста, в конечном итоге получат перекомпилировать и скорректировать TableCardinality при первом запуске, если они не пусты. Масштаб таблицы компиляции хранится в плане, и если оператор выполняется снова с той же мощью (либо из-за потока управляющих инструкций, либо для повторного использования кэшированного плана), не происходит перекомпиляции.

NB2: для кэшированных временных таблиц в хранимых процедурах история перекомпиляции намного сложнее, чем описано выше. См. Временные таблицы в хранимых процедурах для всех подробностей.

перекомпилировать

Как и описанные выше перекомпиляции, основанные на модификации, таблицы #temp также могут быть связаны с дополнительные компиляции просто потому, что они разрешают операции, которые запрещены для переменных таблицы, которые запускают компиляцию (например, DDL-изменения CREATE INDEX, ALTER TABLE)

Блокировка

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

DECLARE @tv_target TABLE (c11 int, c22 char (100))

DBCC TRACEON (1200, -1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE ('A', 100)), (2, REPLICATE ('A', 100))

DBCC TRACEOFF (1200, -1,3604)

Для запросов, которые SELECT из переменных таблицы Paul White указывает на комментарии, что они автоматически появляются с неявным NOLOCK советом. Это показано ниже

DECLARE @T TABLE (X INT);

SELECT X
FROM @T
ВАРИАНТ (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

Выход

*** Дерево вывода: (тривиальный план) ***

        PhyOp_TableScan TBL: @T Bmk (Bmk1000) IsRow: COL: подсказки IsBaseRow1002 (NOLOCK)

Однако влияние этого на блокировку может быть довольно незначительным.

SET NOCOUNT ON;

CREATE TABLE #T ([ID] [int] ИДЕНТИФИКАЦИЯ NOT NULL,
                 [Filler] [char] (8000) NULL,
                 ПЕРВИЧНАЯ КЛАВИАТУРА ([ID] DESC))


DECLARE @T TABLE ([ID] [int] ИДЕНТИФИКАЦИЯ NOT NULL,
                 [Filler] [char] (8000) NULL,
                 ПЕРВИЧНАЯ КЛАВИАТУРА ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I <10000)
НАЧАТЬ
ВСТАВИТЬ В # ЦЕННЫЕ ЗНАЧЕНИЯ
INSERT INTO @T ЦЕННОСТИ ПО УМОЛЧАНИЮ
SET @I + = 1
КОНЕЦ

/* Запустите один раз, поэтому вывод компиляции не появляется в выход блокировки * /
EXEC ('SELECT *, sys.fn_PhysLocFormatter (%% physloc %%) FROM #T')

DBCC TRACEON (1200, 3604, -1)
SELECT *, sys.fn_PhysLocFormatter (%% physloc %%)
FROM @T

РАСПЕЧАТАТЬ '--*--'

EXEC ('SELECT *, sys.fn_PhysLocFormatter (%% physloc %%) FROM #T')

DBCC TRACEOFF (1200, 3604, -1)

ТАБЛИЦА ДРОБЛЕНИЯ#T

Ни один из этих результатов не возвращает в порядке индексного ключа, указывающем, что SQL Server использовал распределение упорядоченного сканирования для обоих.

Я дважды запускал вышеуказанный скрипт, а результаты для второго запуска ниже

Процесс 58, получающий блокировку Sch-S в OBJECT: 2: -1325894110: 0 (класс бит0 ref1): OK

- * -
Процесс 58, получающий блокировку IS на OBJECT: 2: -1293893996: 0 (класс бит0 ref1): OK

Процесс 58, получающий S-блокировку на OBJECT: 2: -1293893996: 0 (класс бит0 ref1): OK

Процесс 58, блокирующий блокировку OBJECT: 2: -1293893996: 0

Выход блокировки для переменной таблицы действительно минимален, поскольку SQL Server просто получает блокировку стабильности схемы для объекта. Но для таблицы #temp она почти такая же легкая, что и для блокировки уровня объекта S. Конечно, при работе с таблицами #temp может быть явно указана подсказка NOLOCK или READ UNCOMMITTED.

Аналогично проблеме с протоколированием внешней транзакции пользователя может означать, что блокировки сохраняются дольше для таблиц #temp. С помощью сценария ниже

--BEGIN TRAN;

    CREATE TABLE #T (X INT, Y CHAR (4000) NULL);

    INSERT INTO #T (X) ЦЕННОСТИ (1)

    SELECT CASE resource_type
             WHEN 'OBJECT' THEN OBJECT_NAME (resource_associated_entity_id, 2)
             WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME (object_id, 2)
                                           FROM tempdb.sys.allocation_units a
                                           JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                                           WHERE a.allocation_unit_id = resource_associated_entity_id)
             WHEN 'DATABASE' THEN DB_NAME (resource_database_id)
             ELSE (SELECT OBJECT_NAME (object_id, 2)
                   FROM tempdb.sys.partitions
                   WHERE partition_id = resource_associated_entity_id)
           END AS имя_объекта,
           *
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@ SPID

    ТАБЛИЦА DROP #T

   - ROLLBACK

при выполнении за пределами явной пользовательской транзакции для обоих случаев единственная блокировка, возвращаемая при проверке sys.dm_tran_locks, является общей блокировкой в ​​DATABASE.

При раскомментировании BEGIN TRAN ... ROLLBACK возвращаются 26 строк, показывающих, что блокировки хранятся как на самом объекте, так и на строках системной таблицы, что позволяет откат и предотвращение чтения других транзакций с незавершенными данными , Операция эквивалентной таблицы переменных не подвержена откату с пользовательской транзакцией и не нуждается в том, чтобы удерживать эти блокировки для нас, чтобы проверить следующий оператор, но блокировки отслеживания, полученные и выпущенные в Profiler, или с помощью флага трассировки 1200 показывают, что множество событий блокировки все еще остаются происходят.

Индексы

Для версий до SQL Server 2014 индексы могут быть созданы неявно только для переменных таблицы как побочный эффект добавления уникального ограничения или первичного ключа. Конечно, это означает, что поддерживаются только уникальные индексы. Неинтерактивный некластеризованный индекс в таблице с уникальным кластеризованным индексом можно смоделировать, просто объявив его UNIQUE NONCLUSTERED и добавив ключ CI в конец нужного ключа NCI (SQL Server будет делать это за кулисами в любом случае , даже если можно указать не уникальный NCI)

Как было показано ранее, в объявлении ограничения можно указать различные index_option s, включая DATA_COMPRESSION, IGNORE_DUP_KEY и FILLFACTOR (хотя в настройке этого нет смысла, так как это только будет иметь значение при перестройке индекса, и вы не сможете перестроить индексы в переменных таблицы!)

Кроме того, переменные таблицы не поддерживают столбцы INCLUDE d, отфильтрованные индексы (до 2016 года) или разделы, таблицы #temp (схема раздела должна быть создана в Tempdb).

Индексы в SQL Server 2014

Нестандартные индексы могут быть объявлены inline в определении переменной таблицы в SQL Server 2014. Пример синтаксиса для этого ниже.

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /* Одиночные столбцы могут быть объявлены рядом с столбцом * /
C2 INT INDEX IX2 НЕРАСПРОСТРАНЕННЫЙ,
       ИНДЕКС IX3 НЕРАСПРОСТРАНЕННЫЙ (C1, C2) /* Пример составного индекса * /
);

Индексы в SQL Server 2016

В CTP 3.1 теперь можно объявить отфильтрованные индексы для переменных таблицы. По RTM это может быть в том случае, если включенные столбцы такжехотя они скорее всего не попадут в SQL16 из-за ограничений ресурсов

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 NOT NULL /* Уникальное игнорирование нулей * /
)

Параллельность

Запросы, которые вставляют (или иным образом изменяют) @table_variables, не могут иметь параллельный план, #temp_tables не ограничены таким образом.

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

INSERT INTO @DATA (...)
EXEC ('SELECT .. FROM ...')

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

Другие функциональные различия

  • #temp_tables не может использоваться внутри функции. @table_variables может использоваться внутри скалярных или многозадачных табличных UDF.
  • @table_variables не может иметь именованных ограничений.
  • @table_variables не может быть SELECT -ed INTO, ALTER -ed, TRUNCATE d или быть целью команд DBCC, таких как DBCC CHECKIDENT или SET IDENTITY INSERT) и не поддерживать подсказки таблицы, такие как WITH ( FORCESCAN)
  • CHECK ограничения на переменные таблицы не рассматриваются оптимизатором для упрощения, предполагаемых предикатов или обнаружения противоречий.
  • Переменные таблицы, похоже, не подходят для оптимизации разделения строк , что означает, что планы удаления и обновления против них могут столкнуться с большим количеством служебных данных и ожиданиями PAGELATCH_EX. ( Пример )

Только память?

Как указано в начале, оба сохраняются на страницах в tempdb. Однако я не рассматривал, было ли какое-либо различие в поведении, когда дело доходило до написания этих страниц на диске.

Я сделал небольшое количество тестов на этом сейчас и до сих пор не видел такой разницы. В конкретном тесте, который я делал на моем экземпляре SQL Server 250, кажется, что это точка отсечения до того, как файл данных будет записан.

  

Примечание. Ниже приведенное поведение больше не встречается в SQL Server 2014 или SQL   Server 2012 SP1 /CU10 или SP2 /CU1 нетерпеливый писатель уже не как   стремятся писать страницы на диск. Более подробную информацию об этом изменении можно найти в SQL   Сервер 2014: скрытый показатель производительности tempdb .

Запуск сценария ниже

CREATE TABLE #T (X INT, Filler char (8000) NULL)
INSERT INTO #T (X)
SELECT TOP 250 ROW_NUMBER () OVER (ORDER BY @@ SPID)
FROM master..spt_values
ТАБЛИЦА DROP #T

И отслеживая запись в файл данных tempdb с помощью Process Monitor, я не видел никого (кроме случайных на странице загрузки базы данных со смещением 73 728). После изменения 250 на 251 я начал видеть записи, как показано ниже.

ProcMon

На скриншоте выше показаны записи на 5 * 32 страниц и одна запись на одной странице, указывающая, что на страницах было записано 161 страницы. При тестировании с табличными переменными я получил ту же точку отсечения 250 страниц. В приведенном ниже сценарии это выглядит по-другому, смотря на sys.dm_os_buffer_descriptors

DECLARE @T TABLE (
  X INT,
  [dba.se] CHAR (8000) NULL)

INSERT INTO @T
            (ИКС)
SELECT TOP 251 Row_number () OVER (ORDER BY (SELECT 0))
FROM master..spt_values

SELECT is_modified,
       Count (*) AS page_count
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND alloc_unit_id = (SELECT a.allocation_unit_id
                                 FROM tempdb.sys.partitions AS p
                               INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                          ON p.hobt_id = a.container_id
                                        INNER JOIN tempdb.sys.columns AS c
                                          ON c.object_id = p.object_id
                                 WHERE c.name = 'dba.se')
GROUP BY is_modified

Результаты

is_modified page_count
----------- -----------
0 192
1 61

Показывая, что 192 страницы были записаны на диск, а грязный флаг очищен. Он также показывает, что запись на диск не означает, что страницы будут немедленно удалены из пула буферов. Запросы к этой переменной таблицы все равно могут быть полностью выполнены из памяти.

На незанятом сервере с max server memory установлен код 2000 MB и DBCC MEMORYSTATUS. Буферные пул-ресурсы, выделенные как приблизительно 1 843 000 КБ (c 23 000 страниц). Я вставил в таблицы выше партии по 1000 строк /страниц и для каждой записанной итерации.

SELECT Count (*)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2
       И alloc_unit_id = @allocId
       AND page_type = 'DATA_PAGE'

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

Страницы в буферном пуле

ответил Martin Smith 12 AMpThu, 12 Apr 2012 02:22:44 +040022Thursday 2012, 02:22:44
36

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

  1. #temp таблицы по умолчанию используют стандартную сортировку экземпляра SQL Server. Поэтому, если не указано иное, вы можете столкнуться с проблемами сравнения или обновления значений между таблицами #temp и таблицами базы данных, если masterdb имеет различную сортировку из базы данных. См .: http://www.mssqltips.com /sqlservertip /2440 /создать-SQL-сервер временной таблицы-с-правильно-сортировка /
  2. Полностью основанный на личном опыте, доступная память, похоже, влияет на то, что работает лучше. MSDN рекомендует использовать табличные переменные для хранения меньших наборов результатов, но большую часть времени разница даже не заметна. Однако в более крупных наборах в некоторых случаях становится очевидным, что переменные таблицы имеют гораздо больший объем памяти и могут замедлить запрос до обхода.
ответил Kahn 7 32012vEurope/Moscow11bEurope/MoscowWed, 07 Nov 2012 17:38:07 +0400 2012, 17:38:07

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132