table (Transact-SQL)table (Transact-SQL)

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

後で処理できるように結果セットを格納するための特別なデータ型です。Is a special data type used to store a result set for processing at a later time. table は主に、テーブル値関数の結果セットとして返される行のセットの一時的な保存に使用します。table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. 型には、関数および変数を宣言することができます テーブルです。Functions and variables can be declared to be of type table. テーブル 変数は、関数、ストアド プロシージャ、およびバッチで使用できます。table variables can be used in functions, stored procedures, and batches. table 型の変数を宣言するには、DECLARE @local_variableを使用します。To declare variables of type table, use DECLARE @local_variable.

適用対象: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 から SQL ServerSQL Server)、Azure SQL データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL ServerSQL Server), Azure SQL データベースAzure SQL Database.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

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

引数Arguments

table_type_definitiontable_type_definition
CREATE TABLE でテーブルを定義するために使用する情報のサブセットと同じです。Is the same subset of information that is used to define a table in CREATE TABLE. テーブルの定義には、列の定義、名前、データ型、制約が含まれます。The table declaration includes column definitions, names, data types, and constraints. 許可される制約の種類は、PRIMARY KEY、UNIQUE KEY、NULL だけです。The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
構文の詳細については、「CREATE TABLE (Transact-SQL)」、「CREATE FUNCTION (Transact-SQL)」、および「DECLARE @local_variable (Transact-SQL)」をご覧ください。For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definitioncollation_definition
MicrosoftMicrosoft Windows ロケールと比較形式、Windows ロケールとバイナリ表記から構成される列の照合順序、または MicrosoftMicrosoft SQL ServerSQL Server 照合順序です。Is the collation of the column that is made up of a MicrosoftMicrosoft Windows locale and a comparison style, a Windows locale, and the binary notation, or a MicrosoftMicrosoft SQL ServerSQL Server collation. collation_definition が指定されていない場合、列は現在のデータベースの照合順序を継承します。If collation_definition isn't specified, the column inherits the collation of the current database. または、列が共通言語ランタイム (CLR) ユーザー定義型として定義されている場合は、ユーザー定義型の照合順序が列に継承されます。Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

RemarksRemarks

以下の例に示すように、バッチの FROM 句の名前による table 参照変数:table Reference variables by name in a batch's FROM clause, as shown the following example:

SELECT Employee_ID, Department_ID FROM @MyTableVar;  

FROM 句では、外部 テーブル 変数は、次の例で示すように、別名を使用して参照する必要があります。Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

SELECT EmployeeID, DepartmentID   
FROM @MyTableVar m  
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND  
   m.DepartmentID = Employee.DepartmentID);  

table 変数では、クエリ プランが変化せず、再コンパイルの影響が大きい場合がある小規模のクエリに対して、次の利点を提供します。table variables provide the following benefits for small-scale queries that have query plans that don't change and when recompilation concerns are dominant:

  • A テーブル 変数は、ローカル変数のように動作します。A table variable behaves like a local variable. この変数には適切に定義されたスコープがあります。It has a well-defined scope. この変数は、それが宣言されている関数、ストアド プロシージャ、またはバッチです。This variable is the function, stored procedure, or batch that it's declared in.
    そのスコープの中、 テーブル 変数は、通常のテーブルのように使用できます。Within its scope, a table variable can be used like a regular table. また、SELECT、INSERT、UPDATE、および DELETE の各ステートメントで、テーブルまたはテーブル式が使用されている任意の場所で適用できます。It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. ただし、table は次のステートメントで使用できません。However, table can't be used in the following statement:
SELECT select_list INTO table_variable;

table 変数は、それが定義されている関数、ストアド プロシージャ、またはバッチの終了時に自動的に削除されます。table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.

  • ストアド プロシージャで table 変数を使用すると、パフォーマンスに影響を与えるコストベースの選択肢がないとき、一時テーブルを使用する場合に比べ、ストアド プロシージャの再コンパイルが少なくなります。table variables that are used in stored procedures cause fewer stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.
  • 使用するトランザクション テーブル で変数が最後の更新の間のみ、 テーブル 変数です。Transactions involving table variables last only for the duration of an update on the table variable. そのため、table 変数の場合、ロックとログに必要なリソースが少なくなります。As such, table variables require less locking and logging resources.

制限事項と制約事項Limitations and restrictions

table 変数には配布の統計情報がありません。Table variables don't have distribution statistics. 再コンパイルをトリガーしません。They won't trigger recompiles. 多くの場合、オプティマイザーはテーブル変数に行がないことを前提としてクエリ プランを構築します。In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. このため、多数の行 (100 行を超える行) を使用する可能性がある場合は、テーブル変数を慎重に使用する必要があります。For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). このような場合、一時テーブルによって問題が解決することがあります。Temp tables may be a better solution in this case. テーブル変数を他のテーブルに結合するクエリの場合は、RECOMPILE ヒントを使用します。このヒントを使用すると、オプティマイザーがテーブル変数に適切なカーディナリティを使用するようになります。For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

table 変数は、SQL ServerSQL Server オプティマイザーのコストベース推論モデルでサポートされていません。table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. したがって、効果的なクエリ プランを実現するためにコストベースの選択が必要な場合は、使用しないでください。As such, they shouldn't be used when cost-based choices are required to achieve an efficient query plan. コストベースの選択が必要な場合は、一時テーブルが推奨されます。Temporary tables are preferred when cost-based choices are required. このプランには通常、結合、並列処理の決定、インデックス選択を含むクエリが含まれます。This plan typically includes queries with joins, parallelism decisions, and index selection choices.

table 変数を変更するクエリでは、並列クエリ実行プランを生成しません。Queries that modify table variables don't generate parallel query execution plans. 大きな table 変数や複雑なクエリの table 変数を変更すると、パフォーマンスに影響が出ることがあります。Performance can be affected when large table variables, or table variables in complex queries, are modified. table 変数が変更されるような状況では、一時テーブルを代わりに使用することを検討してください。Consider using temporary tables instead in situations where table variables are modified. 詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。For more information, see CREATE TABLE (Transact-SQL). 読み取るクエリは テーブル 変数を変更せずの並列処理を実行できます。Queries that read table variables without modifying them can still be parallelized.

table 変数でインデックスを明示的に作成することはできません。table 変数では統計が保持されません。Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 以降では、特定のインデックスの種類をテーブル定義にインライン作成できる新しい構文が導入されました。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. この新しい構文を使うと、テーブル定義の一部としてテーブル変数にインデックスを作成できます。Using this new syntax, you can create indexes on table variables as part of the table definition. 場合によっては、完全なインデックスのサポートと統計を提供する一時テーブルを使用した方が、パフォーマンスが向上する場合があります。In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. 一時テーブルとインライン インデックス作成について詳しくは、「CREATE TABLE (Transact-SQL)」をご覧ください。For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

CHECK 制約、既定値、計算列、table 型の宣言は、ユーザー定義関数を呼び出すことはできません。CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

table 変数間の代入操作はサポートされていません。Assignment operation between table variables isn't supported.

table 変数はスコープが限られており、持続性のあるデータベースに含まれないため、トランザクション ロールバックの影響を受けません。Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

table 変数は作成後に変更できません。Table variables can't be altered after creation.

テーブル変数の遅延コンパイルTable variable deferred compilation

テーブル変数の遅延コンパイルを使用すると、テーブル変数を参照するクエリのプランの品質および全体的なパフォーマンスが向上します。Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. 最適化と最初のプランのコンパイルの実行中に、この機能は実際テーブル変数の行数に基づくカーディナリティの推定を反映します。During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. この正確な行数の情報は、ダウンストリーム プラン操作を最適化するために使用されます。This exact row count information will then be used for optimizing downstream plan operations.

注意

テーブル変数の遅延コンパイルは、Azure SQL データベースAzure SQL Database および SQL Server 2019 (15.x)SQL Server 2019 (15.x) でのパブリック プレビュー機能です。Table variable deferred compilation is a public preview feature in Azure SQL データベースAzure SQL Database and SQL Server 2019 (15.x)SQL Server 2019 (15.x).

テーブル変数の遅延コンパイルを使用すると、テーブル変数を参照するステートメントのコンパイルは、そのステートメントが最初に実際に実行されるまで遅延されます。With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. この遅延コンパイルの動作は、一時テーブルの動作と同じです。This deferred compilation behavior is identical to the behavior of temporary tables. この変更によって、元の 1 行の推定値ではなく、実際のカーディナリティを使用できるようになります。This change results in the use of actual cardinality instead of the original one-row guess.

テーブル変数の遅延コンパイルのパブリック プレビューを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 150 を有効にします。To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you're connected to when the query runs.

テーブル変数の遅延コンパイルを使用することで、テーブル変数のその他の特性が変更されることはありませんTable variable deferred compilation doesn't change any other characteristics of table variables. たとえば、この機能はテーブル変数に列統計を追加しません。For example, this feature doesn't add column statistics to table variables.

テーブル変数の遅延コンパイルによって再コンパイルの頻度が増加することはありませんTable variable deferred compilation doesn't increase recompilation frequency. 最初のコンパイルを行った場所でシフトします。Rather, it shifts where the initial compilation occurs. 結果として得られるキャッシュされたプランは、最初の遅延コンパイルのテーブル変数の行数に基づいて生成されます。The resulting cached plan generates based on the initial deferred compilation table variable row count. キャッシュされたプランは、連続するクエリによって再利用されます。The cached plan is reused by consecutive queries. プランが削除されるか、再コンパイルされるまで再利用されます。It's reused until the plan is evicted or recompiled.

最初のプランのコンパイルに使用される table 変数の行数で示される標準値は、固定行数の推定値とは異なる場合があります。Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. 異なる場合、ダウンストリーム操作が役立ちます。If it's different, downstream operations will benefit. テーブル変数の行数が実行全体で大幅に変化する場合は、この機能を使用してもパフォーマンスが改善しない可能性があります。Performance may not be improved by this feature if the table variable row count varies significantly across executions.

互換性レベルを変更することなく、テーブル変数の遅延コンパイルを無効にするDisabling table variable deferred compilation without changing the compatibility level

table 変数の遅延コンパイルは、データベースの互換性レベル 150 以上を維持しながら、データベースまたはステートメント範囲で無効にします。Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. データベースを発生源とするすべてのクエリ実行に対してテーブル変数の遅延コンパイルを無効にするには、該当するデータベースとの関連で次のサンプルを実行します。To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

データベースを発生源とするすべてのクエリ実行に対してテーブル変数の遅延コンパイルを再度有効にするには、該当するデータベースとの関連で次のサンプルを実行します。To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

DISABLE_DEFERRED_COMPILATION_TV を USE HINT クエリ ヒントとして割り当てることで、特定のクエリに対してテーブル変数の遅延コンパイル無効にすることもできます。You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. 例:For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT  O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE   O_ORDERKEY  =   L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

使用例Examples

A.A. table 型の変数を宣言するDeclaring a variable of type table

次の例では、UPDATE ステートメントの OUTPUT 句で指定される値を格納する table 変数を作成します。The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. この後に、SELECT 内の値、および @MyTableVar テーブルの更新操作の結果を返す 2 つの Employee ステートメントが続きます。Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. INSERTED.ModifiedDate 列の結果が、Employee テーブルの ModifiedDate 列の値と異なります。Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. この違いは、AFTER UPDATE の値を現在の日付に更新する ModifiedDate トリガーが Employee テーブルで定義されることに起因します。This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. ただし、OUTPUT が返す列には、トリガーが起動される前の値が反映されています。However, the columns returned from OUTPUT reflect the data before triggers are fired. 詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。For more information, see OUTPUT Clause (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   
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.B. インライン テーブル値関数を作成するCreating an inline table-valued function

次の例では、インライン テーブル値関数を返します。The following example returns an inline table-valued function. ここでは、店舗に販売された製品ごとに 3 つの列を返します。ProductIDNameYTD Total (今年に入ってからの店舗別合計の集計) です。It returns three columns ProductID, Name, and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

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  

関数を呼び出すには、次のクエリを実行します。To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

参照See also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
ユーザー定義関数User-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
テーブル値パラメーターの使用 (データベース エンジン)Use Table-Valued Parameters (Database Engine)
クエリ ヒント (Transact-SQL)Query Hints (Transact-SQL)