table (Transact-SQL)

後で処理するために結果セットを格納するときに使用する特別なデータ型です。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 を指定しない場合は、現在のデータベースの照合順序が列に継承されます。または、列が共通言語ランタイム (CLR) ユーザー定義型として定義されている場合は、ユーザー定義型の照合順序が列に継承されます。

ベスト プラクティス

テーブル変数を使用して大量のデータ (100 を超える行) を格納しないでください。テーブル変数に大量のデータを格納すると、最適かつ安定したプランの選択ができなくなる可能性があります。一時テーブルを使用するようにこのようなクエリを書き直すことを検討してください。または、USE PLAN クエリ ヒントを使用して、シナリオに適した既存のクエリ プランがオプティマイザーで使用されるようにしてください。

全般的な解説

以下の例に示すように、table 変数は、バッチの FROM 句の名前により参照できます。

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 変数を使用すると、ロックおよびログで必要なリソースが少なくて済みます。

制限事項と制約事項

table 変数は、SQL Server オプティマイザーのコストベースの推論モデルではサポートされていません。したがって、効率的なクエリ プランを実現するためにコストベースの選択が必要な場合は、この変数を使用しないでください。コストベースの選択が必要な場合は、一時テーブルが推奨されます。通常は、結合、並列処理に関する決定、およびインデックスの選択を使用するクエリがこれに含まれます。

table 変数を変更するクエリは、並列クエリの実行プランを生成しません。非常に大きな table 変数、または複雑なクエリ内の table 変数を変更する場合は、パフォーマンスに影響が生じることがあります。このような場合は、代わりに一時テーブルを使用することを検討してください。詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。table 変数を変更せずに読み取るクエリは並列処理できます。

table 変数ではインデックスを明示的に作成することができず、統計が保持されません。場合によっては、インデックスと統計をサポートする一時テーブルを使用した方がパフォーマンスが向上します。一時テーブルの詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。

table 型宣言の CHECK 制約、DEFAULT 値、および計算列は、ユーザー定義関数を呼び出すことができません。

table 変数間における代入操作はサポートされていません。

table 変数は、限られたスコープを持ち、かつ、持続性のあるデータベースの一部ではないため、トランザクション ロールバックには影響されません。

例

A. table 型の変数を宣言する

次の例では、UPDATE ステートメントの OUTPUT 句で指定される値を格納する table 変数を作成します。この後に、@MyTableVar 内の値、および Employee テーブルの更新操作の結果を返す 2 つの SELECT ステートメントが続きます。INSERTED.ModifiedDate 列の結果が、Employee テーブルの ModifiedDate 列の値と異なることに注意してください。これは、ModifiedDate の値を現在の日付に更新する AFTER UPDATE トリガーが、Employee テーブルで定義されるためです。ただし、OUTPUT から返される列には、トリガーが起動される前のデータが反映されます。詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

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. インライン テーブル値関数を作成する

次の例では、インライン テーブル値関数を返します。ここでは、店舗に販売された製品ごとに 3 つの列を返します。ProductID、Name、および YTD Total (今年に入ってからの店舗別合計の集計) です。

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);

関連項目

参照

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (Transact-SQL)

DECLARE @local_variable (Transact-SQL)

クエリ ヒント (Transact-SQL)

概念

ユーザー定義テーブル値関数

テーブル値パラメーター (データベース エンジン)

USE PLAN クエリ ヒントの使用