Pull to refresh

Табличные переменные

Долгое время в SQL сервере в качестве хранения каких-либо промежуточных данных использовались временные таблицы.
Создавались они примерно следующим образом:
create table #t1(tid int,tname nvarchar(80));

Но, начиная с 2005 версии ситуация несколько изменилась.

Появились, так называемые табличные переменные
В чем разница?
Во-первых в объявлении. Для объявления табличной переменной необходимо объявить ее:
declare @t1 table (tid int, tname nvarchar(80))

в дальнейшем использование этой переменной ничем не отличается от той же временной таблицы:
declare @t1 table (tid int, tname nvarchar(80))

insert into @t1 values (10,'Акулина');
insert into @t1 values (20,'Бронислав');
insert into @t1 values (30,'Богдан');
insert into @t1 values (40,'Борислав');

select tid, tname from @t1;


Преимущества:
• Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
• При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
• Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
• Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
• Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)

Недостатки:
• На табличных переменных нельзя создавать некластерные индексы
• Табличные переменные не содержат статистику
• Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
• Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса

В 2008 версии пошли немного дальше… и теперь, для того, что бы использовать какие-нибудь заранее предопределенные данные, уже не нужно создавать ни временную таблицу, ни табличную переменную…
Достаточно обойтись одним оператором value:

select * from (
values (10,'Акулина')
, (20,'Бронислав')
, (30,'Богдан')
, (40,'Борислав')
) t (tid, tname)


или например так:

select max(case when n=1 then id end) id , max(case when n=1 then r end) r1, min(case when n=0 then r end) r2
from
(
select sourceID id , ROW_NUMBER () over (order by sourceID) r
from LoyaltyDiff_BrokerMessagesReceived (nolock)
)t
cross join (values (0) ,(1))nn(n)


Вывод: Используйте новые возможности!
Tags:
Hubs:
You can’t comment this publication because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.