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

本主題適用於:是SQL Server (從 2008 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure 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 is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. 函式和變數可以宣告為類型資料表Functions and variables can be declared to be of type table. 資料表變數可以用於函式、 預存程序和批次。table variables can be used in functions, stored procedures, and batches. 若要宣告類型的變數資料表,使用DECLARE @local_variable To declare variables of type table, use DECLARE @local_variable.

適用於SQL ServerSQL Server ( SQL Server 2008SQL Server 2008目前版本)、 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version), 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_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 )建立函式 (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
由所組成的資料行的定序 MicrosoftMicrosoftWindows 地區設定和比較樣式、 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. 如果將資料行定義為 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

資料表可以批次中的 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 子句,之外資料表變數必須參考使用別名,如下列範例所示: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 variables provide the following benefits for small-scale queries that have query plans that do not change and when recompilation concerns are dominant:

  • A資料表變數的行為類似於本機變數。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.
    其在範圍內,資料表可以使用變數,就像是一般資料表。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. 不過,資料表不能在下列陳述式:However, table cannot be used in the following statement:
SELECT select_list INTO table_variable;

資料表變數會自動清除函式、 預存程序或批次中所定義的結尾。table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

  • 資料表預存程序中使用的變數會造成較少的重新編譯的預存程序比不會影響效能的成本考量選擇時,當使用暫存資料表。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.
  • 交易涉及資料表變數只在進行更新的上次上資料表變數。Transactions involving table variables last only for the duration of an update on the table variable. 因此,資料表變數需要較少的鎖定和記錄資源。Therefore, table variables require less locking and logging resources.

限制事項Limitations and restrictions

資料表變數沒有散發統計資料,它們不會觸發重新編譯的次數。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.

資料表中不支援變數 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.

修改的查詢資料表變數並不會產生平行查詢執行計畫。Queries that modify table variables do not generate parallel query execution plans. 當非常大,可能會影響效能資料表變數,或資料表複雜的查詢中的變數會被修改。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). 讀取的查詢資料表仍可平行處理而不需修改這些變數。Queries that read table variables without modifying them can still be parallelized.

索引無法明確建立資料表變數和任何統計資料會保留在資料表變數。Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. 在某些情況下,改用支援索引和統計資料的暫存資料表可以提升效能。In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. 如需暫存資料表的詳細資訊,請參閱CREATE TABLE (TRANSACT-SQL ).For more information about temporary tables, see CREATE TABLE (Transact-SQL).

檢查條件約束、 預設值和計算資料行中的資料表類型宣告不能呼叫使用者定義函數。CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

指派作業之間資料表不支援變數。Assignment operation between table variables is not supported.

因為資料表變數範圍受到限制,而且不是保存資料庫的一部分,不受交易回復。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.

範例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資料行中值的不同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.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 @local_variable (Transact-SQL)
使用資料表值參數 ( Database Engine )Use Table-Valued Parameters (Database Engine)
查詢提示 (Transact-SQL)Query Hints (Transact-SQL)