table (Transact-SQL)

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

Синтаксические обозначения в 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 (Transact-SQL), CREATE FUNCTION (Transact-SQL) и DECLARE @local_variable (Transact-SQL).

• collation_definition
  Параметры сортировки столбцов, состоящие из поддерживаемых Windows настроек локали Майкрософт и стиля сопоставления, настроек локали Windows и представления чисел в двоичной системе счисления или сортировки Microsoft SQL Server. Если значение аргумента collation_definition не установлено, столбец унаследует параметры сортировки текущей базы данных. Или, если столбец определен как имеющий определяемый пользователем тип среды CLR, он унаследует параметры сортировки этого пользовательского типа.

Рекомендации

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

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

На переменные 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 не поддерживаются в модели обоснования с учетом затрат оптимизатора SQL Server. Поэтому их не следует использовать, когда для реализации эффективного плана запроса требуется делать выбор с учетом затрат. При необходимости в выборе с учетом затрат предпочтительнее использовать временные таблицы. Обычно это верно для запросов с соединениями, решений относительно параллелизма и выбора индекса.

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

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

Ограничения 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 AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

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

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

USE AdventureWorks2008R2;
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 (Transact-SQL)

DECLARE @local_variable (Transact-SQL)

Подсказки в запросах (Transact-SQL)

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

Возвращающие табличное значение определяемые пользователем функции

Возвращающие табличное значение параметры (компонент Database Engine)

Работа с подсказкой USE PLAN