[解決済み】SQL Serverでtempテーブルとテーブル変数の違いは何ですか？

質問

SQL Server 2005では、2つの方法でtempテーブルを作成することができます。

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

または

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

この2つの違いは何でしょうか？ 私は、@tmpがまだtempdbを使用しているのか、それともすべてがメモリ上で起こるのかについて、相反する意見を読んだことがあります。

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

どのように解決するのか？

Temporary Tables (#tmp) と Table Variables (@tmp) にはいくつかの違いがありますが、以下の MSDN のリンクにあるように tempdb の使用はそのうちのひとつではありません。

経験則上、少量から中量のデータと単純な使用シナリオの場合は、テーブル変数を使用する必要があります。(これは大雑把なガイドラインで、もちろん例外もたくさんあります。下記と以下の記事を参照してください)

両者を選択する際のポイントをいくつか紹介します。

• テンポラリーテーブルは実際のテーブルなので、CREATE INDEXなどの操作を行うことができます。もし、大量のデータがあり、インデックスでアクセスした方が速い場合は、テンポラリテーブルが良い選択肢となります。

• テーブル変数は、PRIMARY KEY制約またはUNIQUE制約を使用してインデックスを持つことができます。(一意でないインデックスが必要な場合は、一意制約の最後の列として主キー列を含めるだけです。一意なカラムがない場合は、ID カラムを使用することができます)。 SQL 2014には非ユニークインデックスもある .

• テーブル変数はトランザクションに参加しないし SELECT は暗黙のうちに NOLOCK . 例えば、プロシージャの途中でロールバックする場合、そのトランザクション中に投入されたテーブル変数がそのまま投入されます！トランザクションの動作は非常に便利です。

• Tempテーブルは、ストアドプロシージャを再コンパイルすることになるかもしれません（おそらく頻繁に）。テーブル変数はそうではありません。

• SELECT INTOを使用して一時テーブルを作成することができます。これは、素早く書くことができ（アドホックなクエリに適しています）、一時テーブル構造を前もって定義する必要がないため、時間と共に変化するデータ型に対処することができるかもしれません。

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

• ユーザ定義関数内でテーブル変数を使用すると、その関数をより広く使用することができます（詳細はCREATE FUNCTIONドキュメントを参照してください）。もし関数を書くのであれば、他にやむを得ない理由がない限り、一時テーブルよりもテーブル変数を使用すべきです。

• テーブル変数もテンポラリテーブルもtempdbに保存されます。しかし、（2005年以降の）テーブル変数のデフォルトは現在のデータベースの照合順序であり、tempdbのデフォルトの照合順序を取るtempテーブルとは異なります ( レフ ). つまり、tempテーブルを使用していて、データベースの照合順序がtempdbと異なる場合、tempテーブルのデータとデータベースのデータを比較する場合に問題が発生するため、照合順序の問題に注意する必要があります。

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

さらにいくつかの読み物があります。

• マーティン・スミスの大正解 dba.stackexchange.comにあります。

• MSDN FAQで両者の違いについて解説しています。 https://support.microsoft.com/en-gb/kb/305977

• MDSNブログの記事です。 https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

• 記事 https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• テンポラリテーブルとテンポラリ変数の予期せぬ動作とパフォーマンスへの影響。 SQLblog.comのPaul White