table (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

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

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

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);

См. также

Справочник

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