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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

一种特殊的数据类型,可用于存储结果集以进行后续处理。Is a special data type used to store a result set for processing at a later time. table 主要用于临时存储一组作为表值函数结果集返回的行 。table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. 可将函数和变量声明为 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_variableTo declare variables of type table, use DECLARE @local_variable.

适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.

主题链接图标 TRANSACT-SQL 语法约定Topic 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_definition table_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_definition collation_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 isn't 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

可以在批处理的 FROM 子句中按名称引用 table 变量,如下例所示 :table Reference variables by name in a batch's FROM clause, 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 don't change and when recompilation concerns are dominant:

  • table 变量的行为类似于局部变量 。A table variable behaves like a local variable. 有明确定义的作用域。It has a well-defined scope. 此变量就是在其中声明该变量的函数、存储过程或批处理。This variable is the function, stored procedure, or batch that it's 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 can't 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're defined.

  • 在存储过程中使用 table 变量与使用临时表相比,减少了存储过程重新编译量,并且没有影响性能的基于成本的选择 。table variables that are used in stored procedures cause fewer stored procedure recompilations 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 变量需要较少的锁定和日志记录资源 。As such, table variables require less locking and logging resources.

限制和局限Limitations and restrictions

Table 变量没有分发统计信息 。Table variables don't have distribution statistics. 它们不会触发重新编译。They won't trigger recompiles. 在许多情况下,优化器会生成查询计划,假设 table 变量没有行。In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. 出于这一原因,如果您预计会存在大量行(超过 100 行),那么在使用 table 变量时应小心谨慎。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. 如果查询联接 table 变量和其他表,则可使用 RECOMPILE 提示,这使优化器会对 table 变量使用正确的基数。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.

SQL ServerSQL Server 优化器基于成本的原因模型中,不支持 table 变量 。table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. 因此,在需要基于成本的选择来实现高效的查询计划时,不应使用这些变量。As such, they shouldn't 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 plan typically includes queries with joins, parallelism decisions, and index selection choices.

修改 table 变量的查询不会生成并行查询执行计划 。Queries that modify table variables don't generate parallel query execution plans. 修改大型 table 变量或复杂查询中的 table 变量时,可能会影响性能 。Performance can be affected when large table variables, or table variables in complex queries, are modified. 在需要修改 table 变量的情况下,请改用临时表 。Consider using temporary tables instead in situations where table variables are modified. 有关详细信息,请参阅 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 变量的索引,也不保留 table 变量的任何统计信息 。Indexes can't 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).

table 类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数 。CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

不支持在 table 变量之间进行赋值操作 。Assignment operation between table variables isn't supported.

由于 table 变量作用域有限,并且不是持久数据库的一部分,因而事务回滚不会影响它们 。Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

表变量在创建后就无法更改。Table variables can't 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 exact row count information will then be used for optimizing downstream plan operations.

备注

表变量延迟编译是 Azure SQL DatabaseAzure SQL DatabaseSQL Server 2019 (15.x)SQL Server 2019 (15.x) 中的公共预览版功能。Table variable deferred compilation is a public preview feature in Azure SQL DatabaseAzure SQL Database and SQL Server 2019 (15.x)SQL Server 2019 (15.x).

使用“表变量延迟编译”,引用表变量的语句会延迟编译,直到首次实际执行语句后。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. 此更改导致使用实际基数,而不是原始单行猜测。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're connected to when the query runs.

表变量延迟编译不会更改表变量的任何其他特性 。Table variable deferred compilation doesn't change any other characteristics of table variables. 例如,此功能不会向表变量添加列统计信息。For example, this feature doesn't add column statistics to table variables.

表变量延迟编译不会增加重新编译频率 。Table variable deferred compilation doesn't increase recompilation frequency. 相反,它将转移初始编译出现的位置。Rather, it shifts where the initial compilation occurs. 生成的缓存计划是基于初始延迟编译表变量行计数生成的。The resulting cached plan generates based on the initial deferred compilation table variable row count. 缓存计划由连续查询重复使用。The cached plan is reused by consecutive queries. 会对计划重复使用,直至此计划已逐出或进行重新编译。It's reused until the plan is evicted or recompiled.

用于初始计划编译的表变量行计数表示典型值可能不同于固定的猜测行计数。Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. 如果不同,下游操作会有优势。If it's different, downstream operations will benefit. 如果表变量行计数在整个执行过程中差别很大,则可能无法通过此功能来提升性能。Performance may not be improved by this feature if the table variable row count varies significantly across executions.

在不更改兼容性级别的情况下禁用表变量延迟编译Disabling table variable deferred compilation without changing the compatibility level

可在数据库或语句范围内禁用表变量延迟编译,同时将数据库兼容性级别维持在 150 或更高。Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. 若要对源自数据库的所有查询禁用表变量延迟编译,请在对应数据库的上下文中执行以下示例:To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

若要对源自数据库的所有查询重新启用表变量延迟编译,请在对应数据库的上下文中执行以下示例:To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

此外,还可以通过将 DISABLE_DEFERRED_COMPILATION_TV 分配为 USE HINT 查询提示,为特定查询禁用表变量延迟编译。You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. 例如:For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT  O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE   O_ORDERKEY  =   L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

示例Examples

A.A. 声明一个表类型的变量Declaring a variable of type table

下例将创建一个 table 变量,用于储存 UPDATE 语句的 OUTPUT 子句中指定的值。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 列中的结果与 Employee 表的 ModifiedDate 列中的值不同。Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. 此区别是因为对 AFTER UPDATE 表定义了 ModifiedDate 触发器,该触发器可以将 Employee 的值更新为当前日期。This difference 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.B. 创建内联表值函数Creating an inline table-valued function

下面的示例将返回内联表值函数。The following example returns an inline table-valued function. 对于销售给商店的每个产品,该函数返回三列,分别为 ProductIDName 以及各个商店年初至今总数的累计 YTD TotalIt 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)