Время прочтения: 3 мин.
Табличные переменные:
- Имеют cпециальный тип данных «TABLE»;
- Применяются для краткосрочного сохранения данных в табличном виде;
- Для объявления переменной используется DECLARE
DECLARE @TEST TABLE ([PRODUCT] nvarchar(50),
[PRODUCT_group] int NOT NULL)
-- Добавляем данные в табличную переменную
insert into @TEST ([PRODUCT],[PRODUCT_group])
values ('хлеб',1),
('молоко',2),
('игрушки',3);
-- Смотрим результат
Select * from @TEST
PRODUCT | PRODUCT_GROUP | |
1 | хлеб | 1 |
2 | молоко | 2 |
3 | игрушки | 3 |
- При условии объявления применяются в процедурах, функциях, а также могут быть использованы как обычная таблица;
- Эффективное использование для количества записей от 1 до 1000(рекомендуется до 200);
- Нет возможности изменить после создания
Временные таблицы – обладают всеми свойствами стандартных, за исключением места хранения. Хранятся в tempdb.
- Наименование, доступность и видимость – основная разница с постоянными.
- Наименование начинается с «#»;
- Доступны в рамках одного окна запросов и удаляются при выходе из сеанса;
- Для создания используются конструкции используются стандартные команды;
Create table #test_table (
[date_report] date not null,
[date_month] date not null,
[PRODUCT_group] nvarchar(250) null,
[PRODUCT_group_id] int null,
[PRODUCT] nvarchar(250) null,
[PRODUCT_name] nvarchar(250) null)
select [date_report],
[date_month],
[PRODUCT_group],
[PRODUCT_group_id],
[PRODUCT],
[PRODUCT_name]
into #test_table
from table
- Используются, как промежуточное хранилище данных – результат группировки, отдельные данные, сводные данные и проч. Особенно это удобно, когда необходимо выполнять большое количество расчетов для последующих действий, а удалять их потом не удобно, либо нет времени на это, либо нет лишнего места в БД.
- Имеют возможность индексирования.
Основным отличием между этими инструментами является отсутствие сбора статистики по табличным переменным. Таким образом, при выполнении запроса с использованием временной таблицы MS_SQL сам выбирает, как лучше выбрать данные. С табличными переменными такая возможность отсутствует.
Кроме этого, отсутствие статистики у табличных переменных сказывается и на плане запроса. План запроса считает, что в данных всего одна строка, что в свою очередь влияет на стоимость запроса. При сравнении, стоимость использования табличных переменных минимальна, что искажает реальные данные.
Рассмотрим план запроса на примере:
use WideWorldImporters;
set statistics io, time on;
create table #test
(person_id int primary key clustered,
petname varchar(50));
insert into #test
(person_id, petname)
select person_id, right(petname,4)
from [application].[people]
select p.person_id, p.fullname,
p.phonenumber, test.name
from #test as test
join [application].[people] as p
on test.person_id = p.person_id
select p.person_id, p.fullname,
p.phonenumber, test.name
from @test_var as test
join [application].[people] as p
on test.person_id = p.person_id
Стоимость использования табличных переменных выгодно отличается от варианта, где используется временная таблица. Но план запроса не показывает реальные данные, т.к. при расчете учитываются не все строки, а одна.
Чтобы избежать ошибки при расчете стоимости в плане запроса, используем OPTION (RECOMPLIE).
use WideWorldImporters;
set statistics io, time on;
create table #test
(person_id int primary key clustered,
petname varchar(50));
insert into #test
(person_id, petname)
select person_id, right(petname,4)
from [application].[people]
select p.person_id, p.fullname,
p.phonenumber, test.name
from #test as test
join [application].[people] as p
on test.person_id = p.person_id
select p.person_id, p.fullname,
p.phonenumber, test.name
from @test_var as test
join [application].[people] as p
on test.person_id = p.person_id
option (recompile);
use WideWorldImporters;
set statistics io, time on;
create table #test
(person_id int primary key clustered,
petname varchar(50));
insert into #test
(person_id, petname)
select person_id, right(petname,4)
from [application].[people]
select p.person_id, p.fullname,
p.phonenumber, test.name
from #test as test
join [application].[people] as p
on test.person_id = p.person_id
select p.person_id, p.fullname,
p.phonenumber, test.name
from @test_var as test
join [application].[people] as p
on test.person_id = p.person_id
option (recompile);
В итоге, получаем реальную стоимость при расчете.
Мы рассмотрели порядок создания временных таблиц и табличных переменных, их различие и особенности, область применения, а также порядок построения плана запроса. Кроме этого, разобрались как избежать ошибки при расчете стоимости в плане запроса с использованием табличных переменных.