【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付) (プログラミング学習シリーズ) ミック 翔泳社 売り上げランキング : 5595 Amazonで詳しく見る by AZlink

【SQLServer】ストアドのソースを取得するSQL

■ストアドのソースを取得するSQL
SQLServer2005で旧構文、新構文どちらでも取得できることを確認済み。
ただSQL実行結果をいちいちテキストに手動で保存するのは面倒だから、SQL実行結果をテキストに吐き出すようなツールと連携して使うのが良い。

--ストアドプロシージャの一覧を取得
SELECT name FROM sysobjects WHERE type='P' ORDER BY name;

--ストアドプロシージャのソースを取得
--Create PROCEDUREで出力される模様
--旧構文
SELECT syscomments.text FROM syscomments,sysobjects
WHERE  syscomments.id = sysobjects.id
AND    sysobjects.name = '対象ストアド名'

--新構文
SELECT m.definition
FROM sys.objects as o , sys.sql_modules as m
where o.object_id = m.object_id
and
o.name = '対象ストアド名'

SQLパズル 第2版 プログラミングが変わる書き方/考え方 ジョー・セルコ,Joe Celko,ミック 翔泳社 売り上げランキング : 33502 Amazonで詳しく見る

■SQL内のテーブル名を変数で渡す
execを使う。ストアドは勿論のこと、同じテーブル名が数回出てくるSQLの時も役立つ。

DECLARE @tblname varchar(50)
SET @tblname = 'tbl_TEST'
exec('SELECT * from [' + @tblname + ']')

■BULK INSERT
テーブルにテキストファイルから一括して、入れ込める。便利。
WITH以降のオプションでテキストファイル内の区切り文字は変更可。

参考：MSDN フィールド ターミネータと行ターミネータの指定

BULK INSERT tbl_TEST FROM 'C:\filepass,txt'
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n'

注意点：テキストファイルは、INSERTしたいDB(テーブル)があるとこに配置しないといけない。 
.  

SQLパズル 第2版 プログラミングが変わる書き方/考え方 ジョー・セルコ,Joe Celko,ミック 翔泳社 売り上げランキング : 33502 Amazonで詳しく見る

■SQL Server
テーブルが存在している場合、削除を行う。

もし、tbl_memomemoが存在していた場合、削除を行う...とすると以下のように書く

if exists (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[tbl_memomemo]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tbl_memomemo]


---2011/02/27追記
データベース一覧を取得
select * from sysdatabases

name列にDB名が取得されるから、こんな感じでデータベースの存在確認をすればいいか
select name from sysdatabases where name = ''