table (Transact-SQL)

Статья
03/07/2014

Специальный тип данных, который может быть использован для хранения результирующего набора для обработки в будущем. Переменная table используется в первую очередь для временного хранения набора строк, возвращаемых как результирующий набор функции с табличным значением. Функции и переменные могут быть объявлены как имеющие тип table. Переменные table могут использоваться в функциях, хранимых процедурах и пакетах. Для объявления переменных типа table используйте инструкцию DECLARE @local\_variable.

Применимо для следующих объектов: SQL Server (с SQL Server 2008 по текущую версию), База данных SQL Windows Azure (с первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

table_type_definition ::= 
    TABLE ( { <column_definition> | <table_constraint> } [ ,...n ] ) 

<column_definition> ::= 
    column_name scalar_data_type 
    [ COLLATE <collation_definition> ] 
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ] 
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ] 

<column_constraint> ::= 
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK ( logical_expression ) 
    } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
     | CHECK ( logical_expression ) 
     }

Аргументы

table_type_definition
То же подмножество данных, которое используется для определения таблицы с помощью инструкции CREATE TABLE. Декларация таблицы включает определения столбцов, имен, типов данных и ограничений. К допустимым типам ограничений относятся только PRIMARY KEY, UNIQUE KEY и NULL.

Дополнительные сведения о синтаксисе см. в разделах CREATE TABLE (SQL Server), CREATE FUNCTION (Transact-SQL) и DECLARE @local\_variable (Transact-SQL).
collation_definition
Параметры сортировки столбцов, состоящие из поддерживаемых Windows настроек локали Майкрософт и стиля сопоставления, настроек локали Windows и представления чисел в двоичной системе счисления или сортировки Microsoft SQL Server. Если значение аргумента collation_definition не установлено, столбец унаследует параметры сортировки текущей базы данных. Либо, если столбец определен как имеющий определяемый пользователем тип данных среды CLR, он унаследует параметры сортировки этого определяемого пользователем типа.

Общие замечания

На переменные table можно ссылаться по имени в пакетном предложении FROM, как показано в следующем примере:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Вне предложения FROM на переменные table нужно ссылаться по псевдонимам, как показано в следующем примере:

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

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

Переменная table ведет себя как локальная переменная. Она имеет точно определенную область применения. Это функция, хранимая процедура или пакет, в котором она объявлена.

Внутри этой области переменная table может использоваться как обычная таблица. Она может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE. Однако переменная table не может быть использована в следующей инструкции:
```
SELECT select_list INTO table_variable;
```
Переменные table автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены.
При использовании в хранимых процедурах переменных table приходится прибегать к повторным компиляциям реже, чем при использовании временных таблиц в случае отсутствия необходимости осуществлять выбор, основанный на стоимости, который влияет на производительность.
Транзакции с использованием переменных table продолжаются только во время процесса обновления соответствующей переменной table. Поэтому переменные table реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации.

Ограничения

Переменные Table не имеют статистики распределения. Они не запускают повторных компиляций. Поэтому во многих случаях оптимизатор построит план запроса в предположении, что у табличной переменной нет строк. По этой причине следует проявлять осторожность относительно использования табличной переменной, если ожидается большое число строк (больше 100). В этом случае временные таблицы могут быть предпочтительным решением. В качестве альтернативы для запросов, которые объединяют табличную переменную с другими таблицами, можно использовать указание RECOMPILE, в результате чего оптимизатор будет использовать правильную кратность для табличной переменной.

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

Запросы, изменяющие переменные table, не создают параллельных планов выполнения запроса. При изменении очень больших переменных table или переменных table в сложных запросах может снизиться производительность. В подобных случаях целесообразно рассмотреть возможность использования временных таблиц. Дополнительные сведения см. в разделе CREATE TABLE (SQL Server). Запросы, которые считывают переменные table, не изменяя их, могут выполняться параллельно.

На переменных table нельзя создавать индексы и статистику. В некоторых случаях можно добиться повышения производительности за счет использования вместо табличных переменных временных таблиц, которые позволяют создавать индексы и вести статистический учет. Дополнительные сведения о временных таблицах см. в разделе CREATE TABLE (SQL Server).

Ограничения CHECK, значения DEFAULT и вычисляемые столбцы в декларации типа table не могут вызывать пользовательские функции.

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

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

Табличные переменные нельзя изменить после их создания.

Примеры

А.Объявление переменной типа table

В следующем примере создается переменная типа table, в которой хранятся значения, задаваемые в предложении OUTPUT инструкции UPDATE. Две следующие инструкции SELECT возвращают значения в табличную переменную @MyTableVar, а результаты операции обновления — в таблицу Employee. Заметьте, что результаты в столбце INSERTED.ModifiedDate отличны от значений в столбце ModifiedDate таблицы Employee . Это связано с тем, что для таблицы Employee определен триггер AFTER UPDATE, обновляющий значение ModifiedDate до текущей даты. Однако столбцы, возвращенные из OUTPUT, отражают состояние данных перед срабатыванием триггеров. Дополнительные сведения см. в разделе Предложение OUTPUT (Transact-SQL).

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Б.Создание встроенной функции с табличным значением

Результатом следующего примера является встроенная функция, возвращающая табличное значение. Для каждого из товаров, проданных в магазине, она возвращает три столбца: ProductID, Name и статистику с начала года по магазину — YTD Total .

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

При вызове этой функции выполняется следующий запрос.

SELECT * FROM Sales.ufn_SalesByStore (602);

См. также

Справочник

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (SQL Server)

DECLARE @local\_variable (Transact-SQL)

Указания запросов (Transact-SQL)

Основные понятия

Определяемые пользователем функции

Использование параметров, возвращающих табличные значения (компонент Database Engine)