table (Transact-SQL)

Применимо к:База данныхSQL Server Azure SQL Управляемый экземпляр SQL Azure

table — это специальный тип данных, используемый для хранения результирующий набор для последующей обработки. Тип 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 )
     }

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

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

Remarks

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

SELECT Employee_ID, Department_ID FROM @MyTableVar;

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

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

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

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

  Внутри этой области переменная table может использоваться как обычная таблица. Она может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE. Но переменную table нельзя использовать в следующей инструкции:

SELECT select_list INTO table_variable;

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

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

  Табличные переменные полностью изолированы от создаваемого пакета, поэтому при выполнении инструкции CREATE или ALTER повторное разрешение не требуется, что может происходить с временной таблицей. Временным таблицам требуется повторное разрешение, чтобы на таблицу можно было ссылаться из вложенной хранимой процедуры. Табличные переменные полностью избегают этого шага, поэтому хранимые процедуры могут использовать уже скомпилированный план, что позволяет экономить ресурсы для обработки хранимой процедуры.

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

ограничения

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

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

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

Важно!

Уровень совместимости базы данных 150 повышает производительность табличных переменных с введением отложенной компиляции табличных переменных. См. дополнительные сведения об отложенной компиляции табличных переменных.

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

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

Переменные таблиц нельзя использовать в инструкциях INSERT EXEC или SELECT INTO.

Нельзя использовать инструкцию EXEC или хранимую sp_executesql процедуру для выполнения динамического запроса SQL Server, ссылающегося на табличную переменную, если переменная таблицы была создана вне инструкции EXEC или хранимой sp_executesql процедуры. Поскольку табличные переменные можно ссылаться только в их локальных область, инструкция EXEC и хранимая sp_executesql процедура будут находиться за пределами область табличной переменной. Однако можно создать табличную переменную и выполнить всю обработку внутри инструкции EXEC или sp_executesql хранимой процедуры, так как локальные переменные таблицы область находится в инструкции EXEC или хранимой sp_executesql процедуре.

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

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

Выбор между переменными таблицы и временными таблицами зависит от следующих факторов:

• Количество строк, вставляемых в таблицу.
• Количество перекомпиляций, из которых сохраняется запрос.
• Тип запросов и их зависимость от индексов и статистики производительности.

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

Как правило, по возможности используются табличные переменные, за исключением случаев, когда имеется значительный объем данных и неоднократное использование таблицы. В этом случае можно создать индексы во временной таблице, чтобы повысить производительность запросов. Однако каждый сценарий может отличаться. Корпорация Майкрософт рекомендует проверить, являются ли переменные таблицы более полезными, чем временные таблицы для конкретного запроса или хранимой процедуры.

Примеры

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

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

USE AdventureWorks2019;
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 AdventureWorks2019;
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
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER 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)
• Использование параметров, возвращающих табличные значения (компонент Database Engine)
• Указания запросов (Transact-SQL)