table (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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) ユーザー定義型として定義されている場合は、ユーザー定義型の照合順序が列に継承されます。

解説

以下の例に示すように、バッチの FROM 句の名前による table 参照変数:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

FROM 句では、外部 テーブル 変数は、次の例で示すように、別名を使用して参照する必要があります。

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

クエリ プランが変わらない小規模なクエリの場合や、再コンパイルの影響が大きい場合に、table 変数の方が一時テーブルよりも優れており、以下の利点があります。

• A テーブル 変数は、ローカル変数のように動作します。 この変数には適切に定義されたスコープがあります。 この変数は、それが宣言されている関数、ストアド プロシージャ、またはバッチで使用できます。

  そのスコープの中、 テーブル 変数は、通常のテーブルのように使用できます。 また、SELECT、INSERT、UPDATE、および DELETE の各ステートメントで、テーブルまたはテーブル式が使用されている任意の場所で適用できます。 ただし、table は次のステートメントで使用できません。

SELECT select_list INTO table_variable;

table 変数は、それが定義されている関数、ストアド プロシージャ、またはバッチの終了時に自動的に削除されます。

• ストアド プロシージャで table 変数を使用すると、パフォーマンスに影響を与えるコストベースの選択肢がないとき、一時テーブルを使用する場合に比べ、ストアド プロシージャの再コンパイルが少なくなります。

  table 変数は、それらを作成するバッチから完全に分離されているので、CREATE または ALTER ステートメントの実行時に "再解決" が発生する必要はありません (これは一時テーブルで発生する可能性があります)。 一時テーブルの場合、入れ子になったストアド プロシージャからテーブルを参照できるように、この再解決が必要です。 table 変数を使うとこのステップが完全に回避されるので、ストアド プロシージャは既にコンパイルされているプランを使用できます。その結果、ストアド プロシージャを処理するリソースを節約できます。

• 使用するトランザクション テーブル で変数が最後の更新の間のみ、 テーブル 変数です。 そのため、table 変数の場合、ロックとログに必要なリソースが少なくなります。

制限事項と制約事項

table 変数には配布の統計情報がありません。 再コンパイルをトリガーしません。 多くの場合、オプティマイザーはテーブル変数に行がないことを前提としてクエリ プランを構築します。 このため、多数の行 (100 行を超える行) を使用する可能性がある場合は、テーブル変数を慎重に使用する必要があります。 このような場合、一時テーブルによって問題が解決することがあります。 テーブル変数を他のテーブルに結合するクエリの場合は、RECOMPILE ヒントを使用します。このヒントを使用すると、オプティマイザーがテーブル変数に適切なカーディナリティを使用するようになります。

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

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

重要

データベース互換性レベル 150 では、テーブル変数の遅延コンパイルの導入により、テーブル変数のパフォーマンスが向上します。 詳細については、「テーブル変数の遅延コンパイル」をご覧ください。

table 変数でインデックスを明示的に作成することはできません。table 変数では統計が保持されません。 SQL Server 2014 (12.x) 以降では、特定のインデックスの種類をテーブル定義にインライン作成できる新しい構文が導入されました。 この新しい構文を使うと、テーブル定義の一部としてテーブル変数にインデックスを作成できます。 場合によっては、完全なインデックスのサポートと統計を提供する一時テーブルを使用した方が、パフォーマンスが向上する場合があります。 一時テーブルとインライン インデックス作成について詳しくは、「CREATE TABLE (Transact-SQL)」をご覧ください。

CHECK 制約、既定値、計算列、table 型の宣言は、ユーザー定義関数を呼び出すことはできません。 table 変数間の代入操作はサポートされていません。 table 変数はスコープが限られており、持続性のあるデータベースに含まれないため、トランザクション ロールバックの影響を受けません。 table 変数は作成後に変更できません。

テーブル変数は、SELECT ... INTO ステートメントのINTO句のターゲットとして使用できません。

table 変数が EXEC ステートメントまたは sp_executesql ストアド プロシージャの外部で作成された場合、EXEC ステートメントまたは sp_executesql ストアド プロシージャを使って table 変数を参照する動的 SQL Server クエリを実行することはできません。 table 変数は、そのローカル スコープでのみ参照できるので、EXEC ステートメントと sp_executesql ストアド プロシージャは、table 変数のスコープ外になります。 ただし、table 変数を作成し、EXEC ステートメントまたは sp_executesql ストアド プロシージャの内部ですべてのプロセスを実行できるのは、table 変数のローカル スコープが EXEC ステートメントまたは sp_executesql ストアド プロシージャ内にあるためです。

table 変数はメモリのみの構造体ではありません。 table 変数はメモリに収まらないほどのデータを保持する可能性があるため、ディスク上にデータを配置する場所が必要です。 table 変数は一時テーブルと同様に tempdb データベース内に作成されます。 使用可能なメモリがある場合、table 変数と一時テーブルの両方が作成され、メモリ (データ キャッシュ) 内にある間に処理されます。

table 変数と一時テーブル

table 変数と一時テーブルのどちらを選ぶかは、次の要因によります。

• テーブルに挿入される行数。
• クエリが保存される再コンパイルの回数。
• クエリの種類と、インデックスとパフォーマンスに関する統計情報に対する依存関係。

状況によっては、一時テーブルを含むストアド プロシージャをより小さなストアド プロシージャに分割して、再コンパイルをより小さな単位で行うと便利です。

一般には、データ量が多く、テーブルを繰り返し使う場合を除いて、可能な限り table 変数を使います。 その場合、一時テーブルにインデックスを作成することで、クエリのパフォーマンスを高めることができます。 ただし、各シナリオは異なる場合があります。 Microsoft は、特定のクエリまたはストアド プロシージャに対して、一時テーブルよりも table 変数が役立つかどうかをテストすることをお勧めします。

例

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

次の例では、UPDATE ステートメントの OUTPUT 句で指定される値を格納する table 変数を作成します。 この後に、SELECT 内の値、および @MyTableVar テーブルの更新操作の結果を返す 2 つの Employee ステートメントが続きます。 INSERTED.ModifiedDate 列の結果が、Employee テーブルの ModifiedDate 列の値と異なります。 この違いは、AFTER UPDATE の値を現在の日付に更新する ModifiedDate トリガーが 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. インライン テーブル値関数を作成する

次の例では、インライン テーブル値関数を返します。 ここでは、店舗に販売された製品ごとに 3 つの列を返します。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);

関連項目

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• ユーザー定義関数
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• テーブル値パラメーターの使用 (データベース エンジン)
• クエリ ヒント (Transact-SQL)