SQL, Анализ данных

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

Время прочтения: 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
 PRODUCTPRODUCT_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);

В итоге, получаем реальную стоимость при расчете.

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

Советуем почитать