狠狠撸

SQL Server中級者のための
実践で使えるかもしれないTips集
講師 .NETラボ岡田将

前提知識
? 初心者向けの話はしません。SQLServerでデータベース接続を行って開
発やメンテナンスしたことのある方向けの内容になります。
? 目指す方向性は、メンテナンス性や開発の効率性ですので、速度重視で
はありません。※上級者向けへどうぞ
? 基本的に今までの経験からの独自手法となりますので、バージョン違い
などで、うまく活用できない場合もあります。
? 既に知っている知識はおさらいとして聞き流してください。

アジェンダ
1. テーブル設計手法
2. ちょっと見やすいSQL記述方法
3. ちょっと便利なSQLビュー活用法
4. 常時データはストアドで速度向上
5. 一時テーブルを最大限活用しよう
6. ちょっと便利なデータベース管理技
7. 分析機能が意外と便利

１．テーブル設計手法
データ量や再利用性で考える
作成するテーブルに最大どれだけのデータをため込む予定があるか考えてお
く。
ほぼ直近データしか扱わないなら、バックアップテーブルに退避することも
考えておく。
主キーのメンテナンス性で考える
単一条件ですぐ検索、すぐ更新?削除できるように、マスタなどのアクセス
頻度が高いテーブルは主キーを単一にしておく。（なるべく複合キーにしな
い。もしくは別にくっつけた列を用意する）
オートナンバーを使うとメンテナンス性が低下するけど、やはり便利なので
お好みで。（テーブルの特性で切り替えるのもあり）

１．基本的なテーブル設計手法
即時性で考える（秒、時間、日、月）
トランザクションロック対策として、更新タイミングが重要なデータなのか
を見極める。
商品価格は即時性が重要ですが、商品コメントなどでは即変更しなくても影
響が少ないデータもあるはずです。（WITH(nolock) の活用など）
いっそのこと別のテーブルで即時反映ボタンなど作って、テーブルデータの
同期や作り直す運用もありです。
チューニングは後からでもできる
主キーやトランザクションの単位を間違えなければ、意外と速度の向上のチ
ューニングは何とかなります。（大体SQLの書き方）
まずは作ってしまいましょう。

２．ちょっと見やすいSQL記述方法
Joinではなく、サブクエリで可読性アップ
マスタから名称を１つだけ持ってきたりするのに、サブクエリを使うと可読性
がアップします。
絞り込んだ後のテーブル抽出なので、ちょっとは速度アップ？
同じテーブルから3項目以上持ってくるときは素直にJoinで。
Select
社員ID,
(Select 社員名 From バイト B Where A.社員ID = B.社員ID) AS バイト名,
支払額,
交通費,
レンタル料
From 給与 A

２．ちょっと見やすいSQL記述方法
CASE文をカッコでくくる
()はいくらあってもエラーにはならないので、ちょっとしたものなら
カッコでくくって、1行で記述する。
ちょっとは三項演算子みたいに見えるかも？
CASE文がネストする場合は可読性がよくなる
Select
CASE
WHEN 支払>100 THEN
(CASE WHEN 契約区分 = 1 THEN 200 ELSE 100 END)
ELSE
(CASE WHEN 歩合給 IS NULL THEN 100 ELSE 作業時間 * 10 END)
END AS 給料
From 給与
Select
(CASE WHEN 歩合給 IS NULL THEN 100 ELSE 0 END) AS 給料
From 給与

３．ちょっと便利なSQLビュー活用法
再利用性を考えて、SQLビューを作成しておく
注文情報の表示に毎回商品テーブルから商品名などを取得するのは扱いづらい
ので、すでにJoinしたSQLのビューを作成しておく。
項目が追加になってもビューに追加するだけなので、後付けでISNULL句も入
れられたりメンテナンス性も高い。
※検索速度には注意！
SQLビューは型が確定されていない
Selectの結果によっては、想定していないデータ型を返してくるので
ちゃんと型変換（CastやConvert）をして結果を返すようにする必要あり。

４．常時データはストアドで速度向上
ストアドならサーバー内でキャッシュが使われる
常時使うような検索処理は、ストアドプロシージャ（Stored Procedure）を
使えば、キャッシュが有効に利用される。
追加の絞り込みが発生するようなものはパラメーター指定がしづらいので、
いまいち向かない。
複数テーブルの一括更新にも
プログラム側でも制御可能だけど、SQL側でもストアドならトランザクション
を張ってから複数テーブルの一括更新?ロールバックでのエラー処理の記述が
可能なので、SQLが得意な方はこちらもおすすめ。（プログラム側が楽になる
）

４．常時データはストアドで速度向上
SQLに追加できる関数
常時使う長いCase文や計算式を、ストアドファンクション（Stored Function
）を使ってユーザー定義関数化してしまえば、長いCase文や計算式を毎回書
く必要がなくなる。（制約には注意）
CREATE FUNCTION 勤務時間 (@workhour AS int) RETURNS int
AS
BEGIN
--変数宣言
DECLARE @結果 int;
SET @結果 = (Case When @workhour > 8 THEN 8 ELSE @workhour END);
RETURN @結果;
END
GO
Select dbo.勤務時間(12);

５．一時テーブルを最大限活用しよう
SQLビューでも一時テーブルが使える
パラメーターを渡せないSQLビューですが、一時テーブルを
変数っぽく使うことで、親データを絞り込んで使うことができます。
※値の引き渡しは、一旦テーブルに格納する。
CREATE VIEW VW今月給与
AS
WITH tempTable (column1, colomn2)
AS (
SELECT TOP 1 column1, colomn2 FROM workTable
)
SELECT * FROM T月次給与
WHERE 支払月 = (SELECT colomn1 FROM tempTable)
GO

６．ちょっと便利なデータベース管理技
アタッチ、デタッチで手軽に物理DB移動
簡単に移動させることができます。
また、ログファイルがなくてもアタッチが可能。

SQLデータベースのデタッチの仕方
データベースを右クリック→タスク→デタッチ

ありがちな失敗！

ちゃんと接続の削除の
チェックをいれておく

SQLデータベースのアタッチの仕方
デタッチしたmdfを選択
データベースを右クリック→タスク→アタッチ

保存先を変えるのはここ
Mdf、ldfセットで変える
（デフォルトCドライブなので必須！）

ログファイルの行を選択すると
削除ボタンが有効になる
（mdfファイルの場所にldfが自動で作られる）
ログファイルがない場合や、ログを削除したい場合

６．ちょっと便利なデータベース管理技
メンテナンス時のお手軽切断コマンド
メンテするのにじゃまな接続はsp_whoとkillを使って簡単に解除できる
（作業中の確認やさっきのようなトラブルもこれで解決！）
// 接続状態の確認
sp_who
// 切断したいテーブルのspidが56なら
kill 56

７．分析機能が意外と便利
SQL Management Studioの標準機能が便利
ボトルネックなどの分析ができるツールがビジュアル的に用意されています。
? クライアント統計
? ライブクエリ統計
? 実行プラン

実行プラン
ここの「実行プラン」アイコンをクリックして実行
すると実行プランのタブが増えて出力

実行プラン
実行プランタブを表示すると、テーブルの結合状況や
処理コストが可視化されて表示される
実行結果でのコスト割合が可視化されて表示され、
ソート処理のコストが61％と高いのがわかる

ライブクエリ統計
隣の「ライブクエリ統計」アイコンをクリックして実行
今度はライブクエリ統計のタブが増えて出力

ライブクエリ統計
こちらではコストではなく、件数や処理時間が表示される
ライブクエリ統計タブを表示すると、テーブルの結合状況や
処理時間、件数が可視化されて表示される

クライアント統計
ここの「クライアント統計」アイコンをクリックして実行
今度はクライアント統計のタブが増えて出力

クライアント統計
クライアント統計タブを表示すると、過去実行したSQLの
処理時間やデータ量の比較されて表示される
SQLの記述を変えて、速度改善されたか確認できる
※同じ処理でも実行時間がインデックスやキャッシュで異なる

まとめ
? データベース管理者はもちろん、開発者であっても、データベース接続
を行った開発やメンテナンスは避けられないと思います。
なので、なるべくツールを使いこなして、学習や管理コストを低減しま
しょう。
? なるべくメンテナンス性や開発の効率性を重視することで、SQL側の理
解度は中級までにとどめておけば、他の知識をアプリケーション側など
に集中させることができます。
? チューニングはさらにプロフェッショナルな方々にお愿いしましょう。

ご清聴ありがとうございました
このスライドの内容はノンフィクションであり、
実際に本人に起こったことをもとに作成しています。
また、登場する手法は使えるかもしれませんが、
バージョン違いなどによる動作は一切保証しません。
以上の注意点を確認いただき、用法用量を守って正しくお使いください。

狠狠撸

SQL Server中級者のための実践で使えるかもしれないTips集

Recommended

More Related Content

What's hot (20)

Similar to SQL Server中級者のための実践で使えるかもしれないTips集 (20)

More from Sho Okada (16)

Recently uploaded (15)

SQL Server中級者のための実践で使えるかもしれないTips集