table (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Параметры сортировки столбца, состоящие из языкового стандарта Microsoft 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
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, не изменяя их, могут выполняться параллельно.

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

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

Табличные переменные нельзя использовать в качестве целевого INTO объекта предложения в инструкции SELECT ... INTO .

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

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

Переменные таблицы и временные таблицы

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

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

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

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

Примеры

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

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

USE AdventureWorks2022;
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

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

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

USE AdventureWorks2022;
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)
• Использование параметров, возвращающих табличные значения (ядро СУБД)
• Указания запросов (Transact-SQL)