【SQL】【SQL Server】テンポラリーテーブルとは

SQL Serverにおけるテンポラリーテーブルについて学んだのでメモ。

SQL Serverにおけるテンポラリーテーブルには「ローカル一時テーブル」と「グローバル一時テーブル」の2つがあるらしい。

ローカル一時テーブル
テーブル名の先頭に「#」を付与。
作成したユーザーの現在の接続でのみ表示可能。
このユーザーがSQL Serverのインスタンスから切断すると削除される。

グローバル一時テーブル
テーブル名の先頭に「##」を付与。
作成されるとすべてのユーザーに表示される。
グローバル一時テーブルを参照するすべてのユーザーがSQL Serverのインスタンスから切断すると削除される。

【SQL】【SQL Server】結合して抽出しているテーブルの元データを分かりやすく取る方法2

以前次のような記事を書いた。
stsa.hatenablog.com

この内容でもまあ、目的は達成しているのだけどいかんせんダサい。
何がダサいかって、テーブルごとに範囲を選択して切り取らないといけないので手間だ。

そのため、別の方法を考えてみた。
考えた結果、キー項目を一時テーブルに確保してあげて、そのテーブルを条件に使ってSELECTしたらいいのではという考えに至った。

ではSQLについて。

CREATE TABLE #work1
(id int
 ,name varchar(10)
);

CREATE TABLE #work2
(id int
 ,name varchar(10)
);

INSERT INTO #work1
    ([id], [name])
VALUES
    (1, 'test1');
 INSERT INTO #work1
    ([id], [name])
VALUES
    (2, 'test2');

INSERT INTO #work2
    ([id], [name])
VALUES
    (1, 'test12');
    INSERT INTO #work2
    ([id], [name])
VALUES
    (2, 'test22');
    
 -- id集約用
CREATE TABLE #work_id
(id int
);

  INSERT #work_id
  SELECT #work1.id
  FROM #work1 
  INNER JOIN #work2 
  ON #work1.id = #work2.id
  
  SELECT * FROM #work1 WHERE id IN (SELECT id FROM #work_id)
  SELECT * FROM #work2 WHERE id IN (SELECT id FROM #work_id)

うん。こっちの方が扱いやすいね。

【SQL】【SQL Server】インデックス設計についてまとめてみた

データベースでSQLを扱う際に不可欠なインデックス設計についてまとめてみました。
個人的にSQL Serverを使うことが多いので、割とSQL Server寄りの話になっています。

インデックスとは

・インデックス（索引）は、データベースの性能を向上させる方法の一つ。
・DBの検索を高速に行うための索引のようなもの。
・データ件数が多いテーブルの検索を早くすることができる。
・インデックスの仕組みはBツリーが多い。

インデックスがない場合

・実テーブルの先頭から検索する(Table Scan)

インデックスがある場合

・インデックスを使用して実テーブルの場所を特定してから実データにアクセス(Index Seek)
・テーブルのすべてのデータを検索せずに目的のデータを特定する事ができるため、SQLのパフォーマンス向上に役立つ

インデックスの種類

・クラスタ化インデックス
→テーブル内に1個まで。
→データが物理的に並び替えられる。
→ただし、使用するインデックスによってはヒープを利用してアクセスすることもある。
→主キーを作成すると暗黙でクラスタ化インデックスが作成される。

・非クラスタ化インデックス
→テーブル内に複数作成できる。
→テーブルとインデックスは別物で、実際のテーブルのことをヒープという。
→UNIQUE制約を設定すると暗黙で非クラスタ化インデックスが作成される。

インデックス設定のポイント

・行数が多いテーブルに使用
→データ量が少ないテーブルに使用しても効果はあまりない。
・カーディナリティの高い列に使用する。
→カーディナリティとはデータの種類のこと。
→カーディナリティが低い例：男、女。
→カーディナリティが高い例：IDとか。
・Where句や結合条件に使用されている列にインデックスを設定する。
・更新が少ない列に使用する。
→データの挿入、更新、削除等の処理では索引のメンテナンス作業が発生し、速度の低下につながる。
・INDEXは、必要なものだけにする
→INDEXが多ければ多いほど、update、insert、delete文の性能にも影響してしまう。
→複数インデックスがあると、検索時に想定とは違うインデックスが使用される可能性がある。
(インデックスはオプティマイザが判定して使用する。)

インデックスが使用されないパターン

・インデックス列に演算を行っている
→例 colA * 2.2 > 200
→(置き換えの例)colA > 200/2.2
・索引列に関数を使用している
→関数適用前の値で検索木が形成されているので、検索木をたどることが出来なくなる
・IS NULLを使っている
→DBMSの実装に依存(SQL Serverはインデックスを使用できる)
・否定形を使っている
・ORを使っている
→INを使用する。（INもDBの種類によっては×）
・後方一致、中間一致のLIKEを使用している
→先頭文字が分からないと検索木をたどることが出来なくなるので、使えない
・暗黙の型変換を行っている
→文字列定義のcolAがあったとして。
❌WHERE colA = 10
⭕️WHERE col1A= ‘10’

インデックスが使用されていることを確認する方法

・実行計画を見て確認する。
・SQL ServerならSSMSを使用するなど。

その他

・RID Lookup
→行識別子を元にヒープへアクセスしたことを示している。