資料表 (Transact-SQL)

文章
07/05/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

table 是一種特殊的數據類型，用來儲存結果集以供稍後處理。 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，資料行就會繼承目前資料庫的定序。如果將資料行定義為 Common Language Runtime (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 變數的行為類似於區域變數。它有一個定義妥善的範圍。此變數可用於其宣告所在的函式、預存程序或批次。

在 table 變數的範圍內，您可以依照一般資料表的方式來使用它。在 SELECT、INSERT、UPDATE 和 DELETE 陳述式中，任何使用資料表或資料表運算式的位置都可以套用它。不過在下列陳述式中，不能使用 table：

SELECT select_list INTO table_variable;

在定義 table 變數之函式、預存程序或批次的結尾，會自動清除該變數。

沒有影響效能的成本考量選擇時，預存程序所用之 table 變數造成的預存程序重新編譯，比使用暫存資料表時還少。

數據表變數會完全隔離至建立它們的批次，因此當 CREATE 或 ALTER 語句發生時，就不需要 重新解析 ，而臨時表可能會發生這種狀況。臨時表需要此重新解析，以便從巢狀預存程式參考數據表。數據表變數會完全避免此步驟，因此預存程式可以使用已編譯的計劃，因此可節省資源來處理預存程式。
包含 table 變數的交易，只會在 table 變數更新期間持續存在。因此， 數據表 變數需要較少的鎖定和記錄資源。

限制事項

數據表 變數沒有分佈統計數據。它們不會觸發重新編譯。在許多情況下，優化器會根據數據表變數沒有數據列的假設，建置查詢計劃。基於這個原因，若您預期會有較多數目的資料列 (超過 100 列)，就應該謹慎使用資料表變數。如果是這類情況，暫存資料表也許是更適宜的方案。對於將數據表變數與其他數據表聯結的查詢，請使用 RECOMPILE 提示，這會導致優化器使用數據表變數的正確基數。

SQL Server 最佳化工具的成本考量推論模型不支援 table 變數。因此，需要成本考量選擇來達成有效率的查詢計畫時，就不應該使用這些變數。需要成本考量選擇時，最好使用暫存資料表。這種計畫通常會包含具有聯結的查詢、平行處理原則決定，以及索引選取範圍選擇。

修改 table 變數的查詢不會產生平行查詢執行計畫。修改大型 table 變數或複雜查詢中的 table 變數時，可能會影響效能。在 table 變數遭修改的情況下，請考慮改為使用暫存資料表。如需詳細資訊，請參閱 CREATE TABLE (TRANSACT-SQL)。讀取但不修改 table 變數的查詢仍然可以平行處理。

重要

資料庫相容性層級 150 藉由引進資料表變數延後編譯，改善資料表變數的效能。如需詳細資訊，請參閱資料表變數延遲編譯.

您無法明確建立 table 變數的索引，也無法保留 table 變數的任何統計資料。從 SQL Server 2014 (12.x) 開始，已引進新的語法，允許您建立某些內嵌資料表定義的索引類型。您可以使用這個新的語法在 table 變數上建立索引，作為資料表定義的一部分。在某些情況下，改用完整索引支援和統計資料的暫存資料表可以提升效能。如需暫存資料表和建立內嵌索引的詳細資訊，請參閱 CREATE TABLE (Transact-SQL)。

table 類型宣告中的 CHECK 條件約束、DEFAULT 值和計算資料行無法呼叫使用者定義函式。不支援 table 變數之間的指派作業。由於 table 變數的範圍受到限制，且不是持續性資料庫的一部分；因此，交易回復不會影響它們。資料表變數在建立之後無法修改。

數據表變數不能做為語句中 SELECT ... INTO 子句的目標INTO。

如果數據表變數是在 EXEC 語句或預存程式之外建立，則您無法使用 EXEC 語句或 sp_executesql 預存程式來執行參考數據表變數的 sp_executesql 動態 SQL Server 查詢。因為數據表變數只能在其本機範圍中參考，因此EXEC語句和 sp_executesql 預存程式會超出數據表變數的範圍。不過，您可以建立數據表變數，並在 EXEC 語句或 sp_executesql 預存程式內執行所有處理，因為數據表變數局部範圍位於 EXEC 語句或 sp_executesql 預存程式中。

數據表變數不是僅限記憶體的結構。因為資料表變數保存的資料量可能超出記憶體可容納的資料量，所以在磁碟上必須有一個位置供其儲存資料。數據表變數是在類似於臨時表的 tempdb 資料庫中建立。如果記憶體可供使用，則資料表變數和暫存資料表都會在記憶體內 (資料快取) 建立並進行處理。

數據表變數與臨時表

資料表變數與暫存資料表之間的選擇取決於下列因素：

插入資料表的資料列數目。
儲存查詢的重新編譯次數。
查詢的類型以及其對索引和效能統計資料的相依性。

在某些情況下，將包含暫存資料表的預存程序分割成較小的預存程序，讓重新編譯在較小的單位上進行會很有幫助。

一般而言，請盡可能使用資料表變數，除非有大量的資料且重複使用資料表。在此情況下，您可以在暫存資料表上建立索引以提高查詢效能。不過，每個案例可能有所不同。 Microsoft 建議您測試資料表變數是否比特定查詢或預存程序的暫存資料表更有用。

範例

A. 宣告數據表類型的變數

下列範例會 建立數據表 變數，以儲存 UPDATE 語句之 OUTPUT 子句中指定的值。接下來有兩個 SELECT 語句，其會傳回中的 @MyTableVar 值，以及數據表中 Employee 更新作業的結果。 INSERTED.ModifiedDate 資料行中的結果不同於 Employee 資料表中 ModifiedDate 資料行的值。這項差異是因為將 ModifiedDate 值更新成目前日期的 AFTER UPDATE 觸發程序是定義在 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);

分享方式：