資料表 (Transact-SQL)table (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) 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 Server (SQL Server 2008SQL Server 2008 及更新版本)、Azure SQL DatabaseAzure SQL Database Applies to: ( and later), .

主題連結圖示 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 MicrosoftMicrosoftSQL ServerSQL Server collation. 若未指定 collation_definition,資料行就會繼承目前資料庫的定序。If collation_definition isn't specified, the column inherits the collation of the current database. 如果將資料行定義為 Common Language Runtime (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.

備註Remarks

table 在批次的 FROM 子句中依名稱來參考變數,如下列範例所示: 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. 不過在下列陳述式中,不能使用 tableHowever, 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. 在許多情況下,最佳化工具都將以資料表變數沒有資料列當作假設前提,建立查詢計畫。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 提示,而使最佳化工具針對資料表變數使用正確的基數。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 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.

重要

資料庫相容性層級 150 藉由引進資料表變數延後編譯,改善資料表變數的效能。Database compatibility level 150 improves the performance of table variables with the introduction of table variable deferred compilation. 如需詳細資訊,請參閱資料表變數延遲編譯.For more information, see table variable deferred compilation.

您無法明確建立 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.

範例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. 這項差異是因為將 ModifiedDate 值更新成目前日期的 AFTER UPDATE 觸發程序是定義在 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 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 (Transact-SQL)
使用資料表值參數 (資料庫引擎) Use Table-Valued Parameters (Database Engine)
查詢提示 (Transact-SQL) Query Hints (Transact-SQL)