table (Transact-SQL)table (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Специальный тип данных, который может быть использован для хранения результирующего набора для обработки в будущем.Is a special data type that can be used to store a result set for processing at a later time. Тип table используется в первую очередь для временного хранения набора строк, возвращаемых как результирующий набор функции с табличным значением.table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. Функции и переменные могут быть объявлены как имеющие тип table.Functions and variables can be declared to be of type table. Переменные table могут использоваться в функциях, хранимых процедурах и пакетах.table variables can be used in functions, stored procedures, and batches. Для объявления переменных типа table используйте инструкцию DECLARE @local_variable.To declare variables of type table, use DECLARE @local_variable.

Область применения: SQL ServerSQL Server (начиная с SQL Server 2008SQL Server 2008 до текущей версии), База данных SQL AzureAzure SQL Database.Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version), База данных SQL AzureAzure SQL Database.

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

СинтаксисSyntax

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

АргументыArguments

table_type_definitiontable_type_definition
То же подмножество данных, которое используется для определения таблицы с помощью инструкции CREATE TABLE.Is the same subset of information that is used to define a table in CREATE TABLE. Декларация таблицы включает определения столбцов, имен, типов данных и ограничений.The table declaration includes column definitions, names, data types, and constraints. К допустимым типам ограничений относятся только PRIMARY KEY, UNIQUE KEY и NULL.The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Дополнительные сведения о синтаксисе см. в статьях CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) и DECLARE @local_variable (Transact-SQL).For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definitioncollation_definition
Параметры сортировки столбцов, состоящие из поддерживаемых MicrosoftMicrosoft Windows языкового стандарта и стиля сопоставления, языкового стандарта Windows и двоичной записи или параметров сортировки MicrosoftMicrosoft SQL ServerSQL Server.Is the collation of the column that is made up of a MicrosoftMicrosoft Windows locale and a comparison style, a Windows locale and the binary notation, or a MicrosoftMicrosoft SQL ServerSQL Server collation. Если значение аргумента collation_definition не установлено, столбец наследует параметры сортировки текущей базы данных.If collation_definition is not specified, the column inherits the collation of the current database. Либо, если столбец определен как имеющий определяемый пользователем тип данных среды CLR, он унаследует параметры сортировки этого определяемого пользователем типа.Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

RemarksRemarks

На переменные table можно ссылаться по имени в пакетном предложении FROM, как показано в следующем примере:table variables can be referenced by name in the FROM clause of a batch, as shown the following example:

SELECT Employee_ID, Department_ID FROM @MyTableVar;  

Вне предложения FROM на переменные table нужно ссылаться по псевдонимам, как показано в следующем примере:Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

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

Переменные table предоставляют указанные ниже преимущества для запросов малого масштаба, которые содержат планы запросов и не меняются. Их также рекомендуется использовать, когда соображения повторной компиляции являются доминирующими.table variables provide the following benefits for small-scale queries that have query plans that do not change and when recompilation concerns are dominant:

  • Переменная table ведет себя как локальная переменная.A table variable behaves like a local variable. Она имеет точно определенную область применения.It has a well-defined scope. Это функция, хранимая процедура или пакет, в котором она объявлена.This is the function, stored procedure, or batch that it is declared in.
    Внутри этой области переменная table может использоваться как обычная таблица.Within its scope, a table variable can be used like a regular table. Она может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE.It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. Однако переменная table не может использоваться в следующей инструкции:However, table cannot be used in the following statement:
SELECT select_list INTO table_variable;

Переменные table автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены.table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

  • При использовании переменных table в хранимых процедурах приходится прибегать к повторным компиляциям реже, чем при использовании временных таблиц в случае отсутствия необходимости осуществлять выбор, основанный на стоимости, который влияет на производительность.table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used when there are no cost-based choices that affect performance.
  • Транзакции с использованием переменных table продолжаются только во время процесса обновления соответствующей переменной table.Transactions involving table variables last only for the duration of an update on the table variable. Поэтому переменные table реже подвергаются блокировке и требуют меньше ресурсов для ведения журналов.Therefore, table variables require less locking and logging resources.

ограниченияLimitations and restrictions

Переменные table не имеют статистики распределения. Они не запускают повторных компиляций.Table variables does not have distribution statistics, they will not trigger recompiles. Поэтому во многих случаях оптимизатор построит план запроса в предположении, что у табличной переменной нет строк.Therefore, in many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. По этой причине следует проявлять осторожность относительно использования табличной переменной, если ожидается большое число строк (больше 100).For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). В этом случае временные таблицы могут быть предпочтительным решением.Temp tables may be a better solution in this case. В качестве альтернативы для запросов, которые объединяют табличную переменную с другими таблицами, можно использовать указание RECOMPILE, в результате чего оптимизатор будет использовать правильную кратность для табличной переменной.Alternatively, for queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

Переменные table не поддерживаются в модели выбора с учетом затрат оптимизатора SQL ServerSQL Server.table variables are not supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. Вследствие этого, они не должны использоваться при необходимости осуществления выбора с учетом затрат для получения эффективного плана запроса.Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Временные таблицы являются предпочтительными при необходимости осуществления выбора с учетом затрат.Temporary tables are preferred when cost-based choices are required. Обычно их следует использовать в запросах с соединениями, решениях в отношении параллелизма и при выборе индекса.This typically includes queries with joins, parallelism decisions, and index selection choices.

Запросы, изменяющие переменные table, не создают параллельных планов выполнения запроса.Queries that modify table variables do not generate parallel query execution plans. При изменении очень больших переменных table или переменных table в сложных запросах может снизиться производительность.Performance can be affected when very large table variables, or table variables in complex queries, are modified. В подобных случаях целесообразно рассмотреть возможность использования временных таблиц.In these situations, consider using temporary tables instead. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL).For more information, see CREATE TABLE (Transact-SQL). Запросы, которые считывают переменные table, не изменяя их, могут выполняться параллельно.Queries that read table variables without modifying them can still be parallelized.

Для переменных table нельзя создавать индексы и статистику.Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. Начиная с SQL Server 2014 (12.x)SQL Server 2014 (12.x), реализован новый синтаксис, который позволяет создавать определенные встроенные типы индекса с использованием определения таблицы.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. С помощью этого нового синтаксиса можно создавать индексы в переменной table как часть определения таблицы.Using this new syntax, you can create indexes on table variables as part of the table definition. В некоторых случаях можно добиться повышения производительности за счет использования временных таблиц, которые позволяют работать с индексами и статистикой.In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Дополнительные сведения о временных таблицах и создании встроенных индексов см. в руководстве по использованию CREATE TABLE (Transact-SQL).For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

Ограничения CHECK, значения DEFAULT и вычисляемые столбцы в объявлении типа table не могут вызывать пользовательские функции.CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

Операция присвоения между переменными table не допускается.Assignment operation between table variables is not supported.

Так как переменные table имеют ограниченную область действия и не являются частью постоянной базы данных, они не изменяются в случае откатов транзакций.Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

Табличные переменные нельзя изменить после их создания.Table variables cannot be altered after creation.

Отложенная компиляция табличных переменныхTable variable deferred compilation

Отложенная компиляция табличных переменных позволяет оптимизировать план и повысить общую производительность для запросов, ссылающихся на табличные переменные.Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Во время оптимизации и первичной компиляции плана эта функция будет распространять оценки кратности, основанные на фактическом числе строк табличных переменных.During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Эти точные сведения о числе строк затем будут использоваться для оптимизации последующих операций планирования.This accurate row count information will then be used for optimizing downstream plan operations.

Примечание

Отложенная компиляция табличных переменных предоставляется в Базе данных SQL Azure в режиме общедоступной предварительной версии.Table variable deferred compilation is a public preview feature in Azure SQL Database.

При отложенной компиляции табличных переменных компиляция инструкции со ссылкой на табличную переменную откладывается до момента первого фактического выполнения инструкции.With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Такое поведение при отложенной компиляции идентично поведению временных таблиц. В результате вместо исходного однострочного предположения используется фактическое значение кратности.This deferred compilation behavior is identical to the behavior of temporary tables, and this change results in the use of actual cardinality instead of the original one-row guess.

Чтобы включить общедоступную предварительную версию отложенной компиляции табличных переменных, активируйте режим совместимости базы данных 150 для базы данных, к которой вы подключаетесь при выполнении запроса.To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you are connected to when executing the query.

Отложенная компиляция табличных переменных не изменяет другие характеристики табличных переменных.Table variable deferred compilation does not change any other characteristics of table variables. Например, эта функция не добавляет в табличные переменные статистику по столбцам.For example, this feature does not add column statistics to table variables.

Также эта функция не увеличивает частоту перекомпиляции.Table variable deferred compilation does not increase recompilation frequency. Эта функция эффективна при начальной компиляции.Rather, it shifts where the initial compilation occurs. Итоговый кэшированный план создается на основе числа строк табличных переменных начальной отложенной компиляции.The resulting cached plan is generated based on the initial deferred compilation table variable row count. Кэшированный план повторно используется при выполнении последовательных запросов до тех пор, пока план не будет удален или перекомпилирован.The cached plan is re-used by consecutive queries until the plan is evicted or recompiled.

Если число строк табличных переменных, используемое для начальной компиляции плана, представляет стандартное значение, которое значительно отличается от предположительного фиксированного числа строк, последующие операции будут производительными.If the table variable row count used for initial plan compilation represents a typical value that is significantly different from a fixed row count guess, downstream operations will benefit. Если число строк табличных переменных существенно меняется при каждом выполнении, производительность не будет повышена с помощью этой функции.If the table variable row count varies significantly across executions, then performance may not be improved by this feature.

ПримерыExamples

A.A. Объявление переменной типа tableDeclaring a variable of type table

В следующем примере создается переменная типа table, в которой хранятся значения, задаваемые в предложении OUTPUT инструкции UPDATE.The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Две следующие инструкции SELECT возвращают значения в табличную переменную @MyTableVar, а результаты операции обновления — в таблицу Employee.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Обратите внимание, что результаты в столбце INSERTED.ModifiedDate отличны от значений в столбце ModifiedDate таблицы Employee.Note that the results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. Это связано с тем, что для таблицы AFTER UPDATE определен триггер ModifiedDate, обновляющий значение Employee до текущей даты.This is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Однако столбцы, возвращенные из OUTPUT, отражают состояние данных перед срабатыванием триггеров.However, the columns returned from OUTPUT reflect the data before triggers are fired. Дополнительные сведения см. в статье Предложение OUTPUT (Transact-SQL).For more information, see OUTPUT Clause (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  

Б.B. Создание встроенной функции с табличным значениемCreating an inline table-valued function

Результатом следующего примера является встроенная функция, возвращающая табличное значение.The following example returns an inline table-valued function. Для каждого из товаров, проданных в магазине, она возвращает три столбца: ProductID, Name и статистику с начала года по магазину — YTD Total.It returns three columns ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

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  

При вызове этой функции выполняется следующий запрос.To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

См. также разделSee also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
Определяемые пользователем функцииUser-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
Использование параметров, возвращающих табличные значения (ядро СУБД)Use Table-Valued Parameters (Database Engine)
Указания запросов (Transact-SQL)Query Hints (Transact-SQL)