☰

SQL Server の一時テーブルとテーブル変数の違いは何ですか? 質問する

tag-icon • tag-icon sql-server temp-tables table-variable
SQL Server の一時テーブルとテーブル変数の違いは何ですか? 質問する

SQL Server 2005 では、2 つの異なる方法で同様のテーブルを作成できます。

テーブル変数を使用できます:

declare @tmp table (Col1 int, Col2 int);

または、一時テーブルを使用することもできます。

create table #tmp (Col1 int, Col2 int);

@tmpこれら 2 つの違いは何ですか? tempdb がまだ使用されるのか、すべてがメモリ内で行われるのかに関して、相反する意見を読みました。

どのようなシナリオで、一方が他方よりも優れているのでしょうか?

ベストアンサー1

一時テーブル (#tmp) とテーブル変数 (@tmp) にはいくつかの違いがありますが、以下の MSDN リンクで説明されているように、tempdb の使用はそれらの違いの 1 つではありません。

経験則として、小規模から中規模のデータと単純な使用シナリオでは、テーブル変数を使用する必要があります。(これは、もちろん多くの例外がある非常に広範なガイドラインです。以下およびそれに続く記事を参照してください。)

選択する際に考慮すべき点は次のとおりです。

  • 一時テーブルは実際のテーブルなので、CREATE INDEX などを実行できます。大量のデータがあり、インデックスによるアクセスの方が高速な場合は、一時テーブルが適切なオプションです。

  • テーブル変数には、PRIMARY KEY 制約または UNIQUE 制約を使用してインデックスを設定できます。(一意でないインデックスが必要な場合は、一意制約の最後の列として主キー列を含めます。一意の列がない場合は、ID 列を使用できます。)SQL 2014には非一意インデックスもあります。

  • テーブル変数はトランザクションに参加せず、SELECTは暗黙的に を使用しますNOLOCK。トランザクションの動作は非常に役立ちます。たとえば、プロシージャの途中で ROLLBACK を実行する場合、そのトランザクション中に設定されたテーブル変数は引き続き設定されます。

  • 一時テーブルでは、ストアド プロシージャが頻繁に再コンパイルされる可能性があります。テーブル変数では再コンパイルされません。

  • SELECT INTO を使用して一時テーブルを作成できます。これにより、書き込みが高速化され (アドホック クエリに適しています)、一時テーブルの構造を事前に定義する必要がないため、時間の経過とともに変化するデータ型に対処できるようになります。

  • 関数からテーブル変数を渡すことができるため、ロジックをカプセル化して再利用することがはるかに簡単になります (たとえば、文字列を任意の区切り文字で値のテーブルに分割する関数を作成するなど)。

  • ユーザー定義関数内でテーブル変数を使用すると、それらの関数をより広範囲に使用できるようになります (詳細については、CREATE FUNCTION ドキュメントを参照してください)。関数を作成する場合は、特別な必要がない限り、一時テーブルではなくテーブル変数を使用する必要があります。

  • テーブル変数と一時テーブルは両方ともtempdbに格納されます。ただし、テーブル変数（2005年以降）は、現在のデータベースの照合順序がデフォルトになりますが、一時テーブルはtempdbのデフォルトの照合順序を使用します（参照つまり、一時テーブルを使用する場合、照合順序が tempdb と異なると、一時テーブルのデータとデータベースのデータを比較するときに問題が発生し、照合順序の問題に注意する必要があります。

  • グローバル一時テーブル (##tmp) は、すべてのセッションとユーザーが利用できる別のタイプの一時テーブルです。

さらに詳しい情報:

おすすめ記事