スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

【SQLServer】テーブル変数と一時テーブルについて調べた

【SQLServer】テーブル変数と一時テーブルについて調べた


SQLServerにはテーブル変数と一時テーブルが存在する。
これについて、調べたことがあって参考リンクをメモしておく。


■テーブル変数

table (Transact-SQL) (MSDN)

一時テーブルとの使い分けについて、ベストプラクティスに記載がある。

テーブル変数を使用して大量のデータ (100 行より多く) を格納しないでください。
テーブル変数に大量のデータが含まれると、プランの選択が最適または安定ではなくなる場合があります。 

大量データの処理には向かないということ。
ちなみに変数テーブルは自動的に削除されるため、削除の必要はない。


■一時テーブル

テーブルの作成と変更の基礎(MSDN)
CREATE TABLE (Transact-SQL)(MSDN)

テーブル変数には専用のページがあるけど、一時テーブルには存在しない。
気になるのはここ。

一時テーブルは、tempdb に格納される点と使用されなくなると自動的に削除されるという点を除いて、パーマネント テーブルと同じです。
#employees という名前のローカル一時テーブルが作成された場合、そのセッションだけがテーブルを使用できます。セッションが切断されると、このテーブルは削除されます。

パーマネントテーブルってなんやねん・・・と思ったけど、普通のテーブルを指すようで。
セッションが削除されると、自動的に削除されるようだけど、一時テーブルは、「Drop Table」で明示的に削除可能。

tempdb の sysobjects テーブルに格納される一時テーブルのフル ネームは、CREATE TABLE ステートメントで指定されたテーブル名とシステムが生成する数値サフィックスから構成されます。
「一時テーブルはtempdbを利用します」という公式見解。


■使い方について

SQL Server 一時テーブル・テーブル変数・共通テーブル式(CTE)

構文について一通り記載がある。
このエントリの中に「一時テーブルはtempdbに格納される」と書かれているが、変数テーブルもTempdbを使用している。公式見解には、「一時テーブルとテーブル変数をキャッシュできます。」という記載がある。

tempdb データベース(MSDN)

このtempdbの利用について書いている人がいる。

一時テーブルとテーブル変数使用時の tempdb の利用について(SE の雑記)

一時テーブルとテーブル変数ではインデックスの作成有無やコンパイルへの影響が異なってきますので、どちらを使用するかは状況に応じて検討する必要がありますが、どちらも tempdb が使われるという状況は変わりません。
テーブル変数は変数だからメモリ上に確保される雰囲気がありますが、tempdb が使用されていますので使用する場合はディスクの負荷について考慮したほうがよさそうですね。

自分でもSQLServer2005で検証してみたが、テーブル変数もtempdbを使用しているのは間違いない。
テーブル変数と一時テーブルのパフォーマンスに関わるtempdbのチューニングについては、下記のエントリがよさそう。

tempdb のパフォーマンスの最適化(MSDN)
DB管理者がいますぐ確認すべき3つの設定


■変数テーブルと一時テーブルの違い・使い分けについて

[INF] よく寄せられる質問 - SQL Server 2000 - table 変数(MicroSoftサポート)

SQLServer2000と・・・古い情報だけど変数テーブルと一時テーブルのメリット・デメリットについて書かれてある。
Microsoftさんこれ今でも通用する話なんですよね!?信頼していいんですよね!?
改めて見解をだしてほしいもんだが。

一般的には、膨大な量のデータがあり、テーブルを繰り返し使用するとき以外は、可能な限り table 変数を使用します。その場合、一時テーブルにインデックスを作成すると、クエリのパフォーマンスを向上できます。ただし、状況はそれぞれ異なるため、特定のクエリやストアド プロシージャに対して、table 変数が一時テーブルよりも役立つかどうかをテストすることをお勧めします。

まぁ色々書かれているけど、「検証してみなさい」が総括か。
大量データを全てテーブル変数で処理しているとかあからさまじゃないと、「こっちの方がいい!」って断言ができないなぁ。

▼追記
本記事にコメントしてくださった"はぬ"さんによりますと、パフォーマンスについて、テーブル変数と一時テーブルの一番の違いは、ノンクラスタインデックスが貼れるかどうかにあり、データ量(100行)は関係ないとのことです。
また使い分けに関しては、一時テーブルは定義が柔軟であり、スコープが異なるため、構造により判断することが多いとの助言をいただきました。
ありがとうございました。



SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ) SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)
ミック

翔泳社
売り上げランキング : 5595

Amazonで詳しく見る by AZlink

関連記事
スポンサーサイト

この記事へのコメント

違い - はぬ - 2013年02月01日 22:57:09

パフォーマンスについて、テーブル変数と一時テーブルの一番の違いは、ノンクラスタインデックスが貼れるかどうかです。
100行とか大嘘ですよ。
構造的に、スコープ(ストアドまたぎたい)や、定義の柔軟さで一時テーブル使うことも多いですね。

Re: 違い - ベタログ - 2013年02月02日 22:09:19

意見をくださり、ありがとうございます。
記事を追記させていただきました!

トラックバック

URL :

■スポンサードリンク
■プロフィール

ベタログ

管理人 : ベタログ (Twitterアカウント

思い立ったら、ジャンルを気にせず記事にする。それが自由というものだ。
そんな管理方針。
主に取り扱ってる事:オンラインゲーム(SDGO/Travian/ロードオブナイツ)、プログラミングメモ、ネット小説/書籍の感想/観葉植物など。

■まとめ記事
■カテゴリ
■最新記事
■PICKUP

個人的に押しているモノ


魚里高校ダンジョン部! 藻女神様と行く迷宮甲子園
魚里高校ダンジョン部! 藻女神様と行く迷宮甲子園
■感想記事
これぞ青春エンタメ小説。熱いですよ!

オーバーロード1 不死者の王
オーバーロード1 不死者の王
■感想記事
最強軍団が異世界を蹂躙するファンタジー。Web版も読めますが、凄まじい加筆がされているため、Web版読者も楽しめます。
■お勧め品
■最新コメント
■月別アーカイブ
検索フォーム
リンク
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。