Redgate Hub (日本語)

の一時テーブル

一時テーブルはまさにそれです。これらは、バッチまたはプロシージャ内のデータを処理するときに中間結果のワークスペースを提供するために最も頻繁に使用されます。また、テーブル値関数からテーブルを渡すため、ストアドプロシージャ間でテーブルベースのデータを渡すため、または最近ではテーブル値パラメーターの形式で、アプリケーションからSQLServerルーチンに読み取り専用テーブル全体を送信するためにも使用されます。 、または読み取り専用の一時テーブルをパラメータとして渡します。使用が終了すると、自動的に破棄されます。

テクノロジーについて深く理解する前に、可能な場合はテーブル変数を使用することをお勧めします。それらは簡単で、SQLServerが自動的に機能します。また、勤勉なOLTPシステムに発生する問題が少なくなる傾向があります。たまに、結合でそれらから良好なパフォーマンスを得るためにそれらを微調整する必要があるかもしれませんが、それについてはすぐに説明します。ただし、一時データでより複雑な処理を行う場合、または適度に小さいものを使用する可能性がある場合データ量が多い場合は、ローカル一時テーブルの方が適している可能性があります。

テーブル変数

テーブル変数は、それらが含まれるルーチンまたはバッチのスコープ内で使用されます。定義されており、元々はテーブル値関数を可能にするために作成されました。ただし、これらは、従来の一時テーブルが使用されていた多くの用途に適しています。それらは、スコープ規則で他の変数のように動作します。範囲外になると、それらは破棄されます。これらは操作がはるかに簡単で、非常に安全です。また、一時テーブルを使用する場合よりも、使用されるルーチンでの再コンパイルのトリガーが少なくなります。テーブル変数は、それらを作成したプロセスに「プライベート」であるため、必要なロックリソースが少なくて済みます。テーブル変数はスコープが制限されており、永続データベースの一部ではないため、トランザクションのロールバックはそれらに影響を与えません。したがって、ログエントリなどのロールバック後も存続するはずのデータを作成または保存するのに便利です。テーブル変数の欠点は、デバッグのためにコンテンツを調査したり、さまざまなSQL式をインタラクティブに試したりする前に破棄されることが多いことです。

アプリケーションが保守的で、データ量が少ない場合他に何も欲しくないでしょう。ただし、問題が発生する可能性があります。 1つの問題は、テーブル変数はローカルスコープでのみ参照できるため、一時テーブルやテーブル値パラメーターの場合のように動的SQLを使用して処理できないことです。これは、動的SQLがスコープ外で実行されるため、EXECステートメントまたはsp_ExecuteSQLストアドプロシージャを介して実行する動的SQL内で外部定義のテーブル変数を参照できないためです。テーブル変数の。もちろん、テーブル変数はスコープ内にあるため、動的SQL内でテーブル変数を作成して使用することもできます。ただし、動的SQLを実行すると、テーブル変数はなくなります

注意すべきいくつかの異常もあります。たとえば、最初のDECLAREステートメントの後でテーブル定義を変更することはできません。 SQL Server 2000では、テーブル変数をSELECT INTOステートメントまたはINSERT EXEC（現在は修正済み）の宛先にすることはできません。テーブル変数のCHECK制約、DEFAULT値、および計算列からユーザー定義関数を呼び出すことはできません。 CHECK制約を超えて許可される唯一の制約は、PRIMARY KEY、UNIQUE KEY、およびNULL / NOTNULLです

ただし、SQL Server 2016より前では、テーブルのサイズが大きくなるため、最も難しい問題が発生します。 、インデックスを明示的に宣言できず、UNIQUEおよびPRIMARY KEY制約を適用するインデックスには、分散インデックスが保持されていませんでした。これで、テーブル定義とインラインで特定のインデックスタイプを作成できますが、分布統計はまだ維持されていません。クエリオプティマイザーは、テーブルに1行しかないことを前提としています。また、テーブルの内容を変更しているSQL式の並列クエリプランを生成することもできません。インデックスの制限を部分的に回避するには、制約を使用して同じことを行うことができます。最も重要なのは、クラスター化インデックスを課すことができる主キー制約ですが、一意の制約はパフォーマンスに役立ちます。クエリオプティマイザーは、それらが存在する場合、それらを喜んで使用します。

テーブル変数の最大の問題は、統計が列で維持されないことです。つまり、クエリオプティマイザーはデータのサイズと分布を推測する必要があり、それが間違っていると、結合のパフォーマンスが低下します。これが発生した場合、他にできることはほとんどありません。従来のローカル一時テーブルの使用に戻すよりも。 SQL Server 2019以降、Microsoftは、この問題を解決するTable Variable DeferredCompilationと呼ばれる新機能を導入しました。詳細については、GregLarsenのこの記事をお読みください。

SQL Server 2019を使用していない場合、試すことができる1つのことは、他のテーブルと結合するテーブル変数を含むステートメントにOPTION（RECOMPILE）を追加することです。これを行うことにより、SQL Serverは、行が既に入力されているため、再コンパイル時に行数を検出できるようになります。オプティマイザーにはまだ分布統計がなく、通常は分布が歪んでいる場合、悪い計画を作成する可能性があるため、これで問題が完全に解決されるわけではありません。このデモでは、OPTION（RECOMPILE）を追加するだけで、結合の時間が4分の3短縮されました。

テーブルに入力するものを一意にできる場合は、これらに主キー制約を使用できます。テーブル。これにより、オプティマイザーはテーブルスキャンの代わりにクラスター化インデックスシークを使用でき、実行時間は速すぎて測定できませんでした

テーブル変数から始めますが、パフォーマンスの問題が発生した場合は、ローカル一時テーブルの使用に戻ります。テーブルの行数に関してアドバイスを与えるほど大胆な人もいますが、最大で100または1000が提供されているのを見てきました。しかし、はるかに大きなテーブル変数は時間の経過とともに完全に十分に機能し、はるかに小さなテーブル変数は問題を引き起こすのを見てきました。ただし、テーブルが小さい場合、問題は検出されにくくなります。

テーブル値パラメーター

テーブル値パラメーター（TVP）は、その用途を拡張する特殊なタイプのテーブル変数です。テーブル変数がパラメーターとして渡されると、テーブルはTempDBシステムデータベースでテーブル変数として実体化され、TempDB内のテーブルへのポインターである参照によって渡されます。

テーブル値パラメーターは、 SQL Server 2008は、Transact-SQLルーチンまたはsp_ExecuteSQLを介してバッチに複数行のデータを送信します。プログラマーにとっての特別な価値は、TSQLコード内で次のように使用できることです。クライアントアプリケーションと同様に、クライアントテーブルをサーバーに送信するのに適しています。 TSQLから、テーブル値変数を宣言し、それらにデータを挿入し、これらの変数をテーブル値パラメーターとしてストアドプロシージャおよび関数に渡すことができます。これらのより一般的な有用性は、読み取り専用としてのみ渡されるという事実によって制限されます。テーブル値に対してUPDATE、DELETE、またはINSERTステートメントを実行することはできません。ルーチン本体のパラメータ。

ユーザー定義のテーブルタイプを作成し、それらを使用するためのテーブル構造を定義する必要があります。 TSQLでの使用例を次に示します

テーブル変数と同様に、テーブル値パラメーターはスコープ外になると存在しなくなりますが、タイプ定義は明示的に削除されるまで残ります。テーブル変数と同様に、クライアントからデータが入力されているときにロックを取得せず、テーブル値パラメーターの列の統計が維持されません。テーブル値パラメーターをSELECT INTOまたはINSERT EXECステートメントのターゲットとして使用することはできません。ご想像のとおり、テーブル値パラメーターは、SELECT INTOのFROM句または文字列またはストアドプロシージャ。

TVPは、ローカル変数を動的SQLに渡し、動的SQLがsp_ExecuteSQL。 Microsoftによる文書化が不十分なため、開始するための実際の例を示します

従来の一時テーブルとその使用法について説明する前に、詳細を確認する必要があります。一時テーブルが開催される場所。 TempDB。

TempDB

一時テーブルとテーブル変数はTempDBデータベースに作成されます。これは、実際には単純なリカバリを備えた単なる別のデータベースです。TempDBでは、十分な「最小限の」ロギングのみが実行されます。ロールバック、およびその他のACIDの優れた機能を許可します。 TempDBの特別な違いは、テーブルなどのオブジェクトが起動時にクリアされることです。 TempDBは常に単純なリカバリモデルを使用するため、完了したトランザクションは次のTempDBチェックポイントのログログからクリアされ、ライブトランザクションのみが保持されます。これはすべて、一時テーブルがログに記録され、同じように格納されるという点で、他の種類のベーステーブルと同じように動作することを意味します。実際には、一時テーブルはメモリにキャッシュされたままになる可能性がありますが、頻繁に使用される場合に限ります。ベーステーブルの場合と同じです。 TempDBは、一時オブジェクトの再利用と呼ばれるシステムを運用します。十分なメモリがある場合は、一時オブジェクトの一部をプランとともにキャッシュします。これは、一時オブジェクトがメモリにのみ存在するという伝説を説明している可能性があります。真実は相変わらず「状況によって異なります…」です。

TempDBでは他にも多くのことが行われています。データベースエンジンは、DBCCチェック用の作業テーブルの配置、インデックス、カーソルの作成または再構築に使用できます。例えば。たとえば、「ハッシュ」、「ソート」、「スプール」として記述されたクエリの中間テーブルは、SQLステートメントを実行する際のいくつかの「物理」操作に必要なものとともにTempDBで具体化されます。また、スナップショットアイソレーション、複数のアクティブな結果セット（MARS）、トリガー、online-index-buildのバージョンストアとしても使用されます。

一時テーブルはベーステーブルと同じように保存されるため、1つまたは2つあります。注意が必要なこと。たとえば、通常のテーブルを作成するには、TempDBでCREATE TABLE権限が必要です。手間を省くために、これはデフォルトでDBO（db所有者）ロールに割り当てられていますが、DBOロールが割り当てられていないユーザーに対しては明示的に行う必要がある場合があります。 GUESTユーザーセキュリティコンテキストを介して割り当てられるため、すべてのユーザーはTempDBでローカルまたはグローバル一時テーブルを作成する権限を持っています。

従来の一時テーブルが付属しています2つのフレーバー、接頭辞「##」が付いたグローバルまたは共有可能な一時テーブルと、名前の接頭辞が「＃」であるローカル一時テーブル。ローカル一時テーブルは、グローバル一時テーブルよりも通常のテーブルとは異なります。それらにビューを作成したり、トリガーを関連付けたりすることはできません。どのプロセス、セッション、またはプロシージャがそれらを作成したかを理解するのは少し注意が必要です。これについては、後で少し説明します。最も重要なことは、所有プロセスのみが表示できるため、グローバル一時テーブルよりも安全であるということです。

ローカル一時テーブル（およびローカル一時ストアドプロシージャ）のもう1つの奇妙な点は、名前が異なることです。メタデータで、ルーチンまたはバッチで指定したものに変換します。同じルーチンが複数のプロセスによって同時に実行される場合、データベースエンジンは、異なるプロセスによって作成された同じ名前のローカル一時テーブルを区別できる必要があります。これは、アンダースコア文字が左に埋め込まれた各ローカル一時テーブル名に数値文字列を追加することによって行われます。 #MyTempTableなどの短い名前を指定しますが、TempDBに実際に格納されるのは、CREATE TABLEステートメントで指定されたテーブル名で構成されます。と接尾辞。このサフィックスがあるため、ローカルの一時テーブル名は116文字以下である必要があります。

何が起こっているかを確認したい場合は、他のテーブルと同じようにTempDBでテーブルを表示できます。テーブル。 TempDBから呼び出す場合にのみ、一時テーブルでsp_help作業を使用することもできます。

または、データベースを切り替えずにTempDBのシステムビューで見つけることができます。

または情報スキーマ

さらに良いことに、 TempDBの巨大な一時テーブルを保持し、スペースを放棄することを拒否しているプロセスとユーザーを確認します。

TempDBにデータ型が存在しない限り、一時テーブルでユーザー定義のデータ型を使用することはできません。つまり、データ型が明示的に作成されていない限り

TempDBのユーザーテーブル

通常の使用では、一時テーブルまたはテーブル変数をあまり深く考えずに作成します。ただし、TempDBがあらゆる種類のサンドボックスアクティビティに使用できるのは興味深いことです。通常のベーステーブル、ビュー、またはその他の必要なものを作成できます。スキーマ、ストアドプロシージャなどを作成できます。これを実行する可能性は低いですが、TempDBは単なる別のデータベースであるため、確かに可能です。 AdventureWorksをインストールして自分自身にこれを証明した後、開発SQLServerを再起動する必要がありました。これは、TempDBにベーステーブルを作成できることを意味します。これは一種の..er…一時的な永続テーブルです。グローバル一時テーブルとは異なり、すべて自分でハウスキーピングを行う必要があります。自分で行います。同じことがルーチンにも当てはまります。これを行う利点は、実行するすべての処理でTempDBの単純なリカバリが使用されるため、モップアップに失敗した場合、SQL Serverが次回の起動時にマザーとして機能することです。ただし、これには非常に長い時間がかかる可能性があります。次の段階は、私が「永続的な一時」テーブルと呼ぶものを用意することです。このテーブルでは、サーバーの再起動時にデータ自体は揮発性ですが、テーブル自体は存続します。永続的な一時テーブルを作成する最も一般的な方法は、起動時にグローバル一時テーブルを再作成することです。これは、すべてのデータベースが回復され、「回復が完了しました」というメッセージがログに記録されたときに自動的に実行できます。これは「グローバル一時」ですが、それを実行するプロセスが原因で、それを使用するすべての接続が消えても削除されません。間違いなく、この種の作業テーブルを使用するデータベースに作成することをお勧めしますが、完全リカバリを使用している場合は、一時的な作業がログに残ります。もちろん、通常の作業テーブルを作成するだけでもかまいません。 TempDBのテーブル。グローバル一時テーブルを作成するマスターでストアドプロシージャを定義することにより、起動時にこれらの「永続的な」テーブルを作成できます。

この種のハイブリッドテーブルを使用する理由たとえば、いくつかあります。データに対して一連の処理を行うために、マルチプロセスセーフな方法で「永続的な」テーブルを介してプロシージャ間でテーブルを渡すためのテクニックの例。これらは、プロセスキー付きテーブルと呼ばれます（「ストアドプロシージャ間でデータを共有する方法」を参照）。 ：ErlandSommarskogによるプロセスキー付きテーブル）。それらは最初は経験豊富なDBAの眉をひそめますが、適切に行われると、永続的な問題に対する効果的かつ安全な解決策になります。

一時的なテーブルだけでなく、いくつかのテーブルタイプもあります。 「偽の」テーブルや派生テーブルなどのベーステーブルから直接派生したものではありません。これらの一部は非常に一時的なものであるため、一時的ではなく一時的なものと見なすのが最適です。 CTEは、「インライン」または「派生」であり、具体化されていないエフェメラルテーブルを使用します。 BOLは、それらを「一時的な名前付き結果セット」と呼びます。それらは式の範囲内にのみ存在します。 CTEでは、複数回アクセスできるという点で、派生テーブルよりも優れています。

ローカル一時テーブル

ローカル一時テーブル（＃で始まる名前）を使用すると、内部で行われていることは、驚くほどテーブル変数に似ています。テーブル変数と同様に、ローカル一時テーブルはそれを作成したプロセス専用です。したがって、これらをビューで使用したり、トリガーを関連付けたりすることはできません。

SELECT INTOを使用して作成したい場合は、テーブル変数よりも便利ですが、SELECT INTO変更が必要になる可能性のあるシステムでは、必要なすべての制約とともに、一時テーブルを明示的に作成したいと思います。

どのセッションまたはプロシージャが作成されたかを簡単に判断することはできません。これらのテーブル。これは、同じストアドプロシージャが複数のプロセスによって同時に実行される場合、データベースエンジンは、異なるプロセスによって作成された同じテーブルを区別できる必要があるためです。データベースエンジンは、各ローカル一時テーブル名に左パディングされた数値サフィックスを内部的に追加することによってこれを行います。 TempDBのsys.objectsビューに格納されている一時テーブルのフルネームは、CREATE TABLEステートメントで指定されたテーブル名とシステムで生成された数値のサフィックスで構成されます。接尾辞を使用できるようにするには、ローカル一時名に指定するテーブル名を116文字未満にする必要があります。

ローカル一時テーブルを使用してハウスキーピングを実行します。 DROP TABLEを使用して明示的に削除されない限り、スコープ外になると自動的に削除されます。それらのスコープはテーブル変数よりも寛大であるため、バッチ内または動的SQLでそれらを参照する際に問題は発生しません。ローカル一時テーブルは、現在のセッションまたはプロシージャの終了時に自動的に削除されます。作成したプロシージャの最後にドロップすると、ヘッドスクラッチが発生する可能性があります。ストアドプロシージャまたはセッション内に作成されたローカル一時テーブルは、終了時にドロップされるため、ストアドプロシージャを呼び出したプロセスから参照できません。テーブルを作成しました。ただし、テーブルを作成したストアドプロシージャによって実行されるネストされたストアドプロシージャから参照できます。ネストされたプロシージャが一時テーブルを参照し、その時点で同じ名前の2つの一時テーブルが存在する場合、クエリはどちらのテーブルに対して解決されますか？

好奇心として、ローカルの一時ストアドプロシージャを作成することもできます。ローカル一時テーブルと同じスコープと有効期間。他のルーチンでも同じことはできません。

グローバル一時テーブル。

ローカル一時テーブルと同様に、グローバル一時テーブル（##で始まる）は、テーブルを作成したセッションが終了すると自動的に削除されます。ただし、グローバルテーブルのためは、それを作成したプロセス専用ではありません。その後、作成セッションの終了時にテーブルをアクティブに参照していた最後のTransact-SQLステートメントが実行を終了し、ロックが解除されるまで存続する必要があります。これらのグローバル一時テーブルが存在するときにTempDBにアクセスできる人は誰でも、これらの一時オブジェクトを直接クエリ、変更、または削除できます。

ルール、デフォルト、およびインデックスを一時テーブルに関連付けることはできますが、ビューを作成することはできません。一時テーブルで、またはトリガーをそれらに関連付けます。一時テーブルを作成するときにユーザー定義のデータ型を使用できるのは、データ型がTempDBに存在する場合のみです。

ストアドプロシージャは、現在のセッション中に作成された一時テーブルを参照できます。ストアドプロシージャ内では、一時テーブルを作成してドロップしてから、同じ名前で新しい一時テーブルを作成することはできません。

これは機能しますが…。

…これは機能しません。 t

ローカル一時テーブルを使用すると、ストアドプロシージャを使用するたびに、意図せずに再コンパイルを強制することができます。ストアドプロシージャがうまく機能しない可能性があるため、これは適切ではありません。再コンパイルを回避するには、呼び出し元または呼び出されたストアドプロシージャで作成された一時テーブルを参照しないでください。参照できない場合は、参照を文字列に入れて、EXECUTEステートメントまたはsp_ExecuteSQLストアドプロシージャ。また、一時テーブルが参照される前にストアドプロシージャまたはトリガーで作成され、これらの参照の後に削除されることを確認してください。IF... ELSEやWHILEなどのフロー制御ステートメント内に一時テーブルを作成しないでください。

グローバルな一時ストアドプロシージャを作成することは許可されていますが、その用途はまだ見つかりません。グローバルな一時的な機能は許可されていません。

結論

共有の遊び場では、バットの振り方に十分注意してください。これを読んでいると、TempDBで多くのアクティビティが発生していることに気付くでしょう。また、一時テーブルの種類に関係なく、不要な量の一時テーブルを埋める長時間実行プロセスを使用すると、SQLServer全体に大混乱を引き起こす可能性があります。データ。実際、この記事では、貴重な共有リソースであるTempDBを軽率に使用することで、DBAを本当に本当に混乱させる方法の手がかりを提供しました。 （S2005以前の昔は、巨大なテーブルでSELECT INTOを使用することは素晴らしいV兵器（Vergeltungswaffe）でした

私は常に過剰な提供に警戒しています-一般的なアドバイスですが、私は常にデータベースでテーブル変数とTVPを使用することを好みます。それらは必要なリソースが少なく、使い終わったときにそれらを保持する可能性が低くなります。私はそれらを最大限に使用するのが好きです。 、列とテーブルのチェックと制約があります。特にテーブルのサイズが大きくなると、蒸気が不足する場合があります。このような場合、またはスコープが制限されているためにテーブル変数を使用することが実用的でない場合は、ローカルの一時テーブルを使用します。グローバルの一時テーブルまたは永続的な一時テーブルに同意するまでには、すぼめた唇と頭を振る必要があります。いくつかの有効で完全に合理的な使用法がありますが、必要なハウスキーピングを行うプログラマー