資料表 (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) 使用者定義類型，資料行便會繼承使用者定義類型的定序。

最佳作法

請勿使用資料表變數來儲存大量資料 (超過 100 個資料列)。當資料表變數包含大量資料時，計畫選擇可能不是最佳或穩定的方式。請考慮將這類查詢重寫成使用暫存資料表或使用 USE PLAN 查詢提示，確保最佳化工具針對您的案例使用適當的現有查詢計畫。

一般備註

批次之 FROM 子句中的名稱可以依照下列範例來參考 table 變數：

SELECT Employee_ID, Department_ID FROM @MyTableVar;

在 FROM 子句之外，您必須依照下列範例所示，利用別名來參考 table 變數：

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
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 變數所造成的預存程序重新編譯，比使用暫存資料表時還少。

• 包含 table 變數的交易，只會在 table 變數更新期間持續存在。因此，table 變數比較不需要鎖定和記錄資源。

限制事項

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

修改 table 變數的查詢不會產生平行查詢執行計畫。修改非常大的 table 變數或複雜查詢中的 table 變數時，可能會影響效能。在這些狀況中，請改用暫存資料表。如需詳細資訊，請參閱＜CREATE TABLE (Transact-SQL)＞。讀取但不修改 table 變數的查詢仍然可以平行處理。

您無法明確建立 table 變數的索引，也無法保留 table 變數的任何統計資料。在某些情況下，改用支援索引和統計資料的暫存資料表可以改進效能。如需有關暫存資料表的詳細資訊，請參閱＜CREATE TABLE (Transact-SQL)＞。

table 類型宣告中的 CHECK 條件約束、DEFAULT 值和計算資料行無法呼叫使用者定義函數。

不支援 table 變數之間的指派作業。

由於 table 變數的範圍受到限制，且不是保存資料庫的一部分，因此，它們不會受交易回復的影響。

範例

A. 宣告資料表類型的變數

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

B. 建立嵌入資料表值函式

USE AdventureWorks2008R2;
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

SELECT * FROM Sales.ufn_SalesByStore (602);

請參閱

參考

概念