table (Transact-SQL)

[アーティクル]
09/10/2013

後で処理できるように結果セットを格納するために使用できる特別なデータ型です。 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) ユーザー定義型として定義されている場合は、ユーザー定義型の照合順序が列に継承されます。

全般的な解説

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

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

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

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

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

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

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

table 変数は、作成後に変更できません。

使用例

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

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

関数を呼び出すには、次のクエリを実行します。

SELECT * FROM Sales.ufn_SalesByStore (602);

table (Transact-SQL)

構文

引数

全般的な解説

制限事項と制約事項

使用例

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

B. インラインテーブル値関数を作成する

関連項目

参照

概念

その他のリソース

table (Transact-SQL)

構文

引数

全般的な解説

制限事項と制約事項

使用例

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

B. インライン テーブル値関数を作成する

関連項目

参照

概念

その他のリソース

B. インラインテーブル値関数を作成する