[解決済み】SQLサーバーでテーブル変数と一時テーブルを使用するのはいつですか？

質問

テーブル変数について詳しく教えてください。tempテーブルは常にディスク上にあり、テーブル変数はメモリ上にある、つまり、tempテーブルよりもテーブル変数の方がIOオペレーションが少ないのでパフォーマンスが良い、と書かれています。

しかし、テーブル変数のレコード数が多すぎてメモリに収まらない場合、テーブル変数がテンポラリテーブルのようにディスクに置かれることがあります。

しかし、このquot;too many records"が何なのかがわかりません。100,000レコード？それとも1000,000レコード？使用しているテーブル変数がメモリ上にあるのか、ディスク上にあるのかを知るにはどうしたらいいでしょうか？SQL Server 2005には、テーブル変数の規模を測定したり、テーブル変数がメモリからディスクに置かれたときに知らせてくれる関数やツールはありますか？

解決方法は？

この質問から、テーブル変数とテンポラリ・テーブルに関する一般的な誤解に陥っていることがわかります。

私は次のように書きました。 DBAのサイトでは、かなり広範囲に渡って回答しています この2つのオブジェクトタイプの違いを見てみましょう。これは、ディスクとメモリの違いについての質問にも対応しています（両者の動作に大きな違いは見られませんでした）。

タイトルにある、テーブル変数とローカルテンポラリーテーブルの使い分けについてですが、常に選択できるわけではありません。例えば関数ではテーブル変数しか使えませんし、子スコープでテーブルに書き込む必要がある場合は #temp テーブルを使用します。 (テーブル値パラメータでは 読み取り専用アクセス ).

選択できる場合は、以下にいくつかの提案を示します（ただし、最も確実な方法は、特定のワークロードで両方を単純にテストすることです）。

1. テーブル変数に作成できないインデックスが必要な場合は、もちろん #temporary テーブルを使用します。しかし、この詳細はバージョンに依存します。SQL Server 2012 以降では、テーブル変数に作成できるインデックスは UNIQUE または PRIMARY KEY 制約があります。SQL Server 2014 では、インライン インデックス構文が導入され、以下のオプションのサブセットで使用できます。 CREATE INDEX . これは、フィルタリングされたインデックス条件を可能にするために、その後拡張されました。インデックスに INCLUDE -しかし、テーブル変数にインデックスを作成することはまだできません。

2. もし、テーブルから大量の行を追加したり削除したりすることを繰り返すのであれば #temporary テーブルを使用します。これは TRUNCATE (よりも効率的です）。 DELETE の後に続けて挿入することができます。 TRUNCATE の後に続くものよりも良いパフォーマンスを発揮することができます。 DELETE この図のように .

3. もし、大量の行を削除したり更新したりするのであれば、temp テーブルはテーブル変数よりもはるかに良い性能を発揮するかもしれません - それが行セットの共有を使用できるのであれば（例については下記の "Effects of rowset sharing" を参照）。
4. テーブルを使用した最適なプランがデータによって異なる場合、テーブルを使用するために #temporary テーブルを使用します。これは、データに応じて計画を動的に再コンパイルすることを可能にする統計の作成をサポートします (ただし、ストアドプロシージャのキャッシュされた一時テーブルでは リコンパイル動作 を別途理解する必要がある)。
5. テーブルを使用する問い合わせの最適な計画が変更される可能性が低い場合、統計情報の作成と再コンパイルのオーバーヘッドをスキップするために、テーブル変数を考慮することができます（望む計画を修正するためのヒントが必要になるかもしれません）。
6. テーブルに挿入されるデータのソースが、潜在的に高価な SELECT ステートメントを使用する場合、テーブル変数を使用すると、並列プランを使用してこの可能性をブロックすることを考慮してください。
7. 外部ユーザートランザクションのロールバック後もテーブルのデータが必要な場合、テーブル変数を使用します。この使用例としては、長いSQLバッチの異なるステップの進行状況をロギングすることが考えられます。
8. を使用する場合 #temp テーブルのロックはテーブル変数よりも長く (ロックの種類と分離レベルによってはトランザクションの終わりかステートメントの終わりまで) 保持される可能性があります。 tempdb トランザクションログは、ユーザートランザクションが終了するまで そのため、テーブル変数を使用する方が有利になる場合があります。
9. ストアドルーチン内では、テーブル変数とテンポラリテーブルの両方をキャッシュすることができます。キャッシュされたテーブル変数のメタデータのメンテナンスは #temporary テーブルがあります。Bob Wardは彼の tempdb プレゼンテーション このため、同時実行性が高い状況では、システムのテーブルにさらなる競合が発生する可能性があること。さらに、少量のデータを扱う場合、このようなことが原因で パフォーマンスに対する測定可能な差 .

行セット共有の効果

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T