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