開発者の方向けの Sql server(db) t sql 振り返り
-
Upload
oda-shinsuke -
Category
Technology
-
view
1.251 -
download
0
Transcript of 開発者の方向けの Sql server(db) t sql 振り返り
![Page 1: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/1.jpg)
「開発者の方向けの」SQL Server / T-SQL 振り返り
CLR/H #clrh101 ~日本の夏、MR の夏~
2016/7/2 SQLWorld お だ
![Page 2: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/2.jpg)
自己紹介
織田 信亮(おだ しんすけ)
大阪で開発者しています
SQLWorld の代表です
http://odashinsuke.hatenablog.com/
Twitter:@shinsukeoda
![Page 3: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/3.jpg)
SQLWorld とは
http://sqlworld.org/
Twitter:@SQLWorld_JP
次のような情報を発信しているコミュニティです
MS の RDBMS である「SQL Server」
もちろん他の DB の話しも!
正規化/モデリング
SQL/NoSQL
![Page 4: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/4.jpg)
World と名乗っていますが、
Worldwide
では無いです!
![Page 5: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/5.jpg)
関西限定!
大阪で勉強会を
開催しています
![Page 7: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/7.jpg)
このセッションの対象者
現場でSQL を書いている
開発者の方
SQL Server じゃなくても良いよ
SQL Server の使用経験が…
使ったことない!
昔のなら使ったことある
新しいのなら使ったことある
![Page 8: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/8.jpg)
このセッションの内容
開発者の方のためのセッション!
Standard 以下で使えるものを中心に
管理 / 運用 のお話しは無し
専門外なので
知ってると開発時に便利な機能を pickup
T-SQL の構文 / 関数 等の紹介
SQL Server 2005 ~ 2016 まで順に紹介
名前だけ憶えてもらえればOK!
必要になったら詳細調べてね~
![Page 9: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/9.jpg)
アジェンダ
SQL Server 2005
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016 / SQL Database
![Page 10: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/10.jpg)
SQL Server 2005
![Page 11: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/11.jpg)
SQL CLR
T-SQL ではなく、CLR(C# や VB.NET)で、ストアドや関数、トリガー等を実装出来るしくみ
SQL Server のプロセス内で実行される
プロセス間通信とかしないよ
実際にどれ位使われてるのかは不明。。
使ってる人います?
![Page 12: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/12.jpg)
クエリ通知 (Query Notification)
ADO.NET で利用可能
SELECT / EXECUTE に対して設定可能
使える構文の制限がきつい
対象のデータが変更されると、結果がSQL Server から PUSH されるイメージ
昔は ASP.NET の cache で使われてる感じ(専用のクラスも用意されてた)
![Page 13: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/13.jpg)
OUTPUT 句
追加/更新/削除 された行の情報を取得
INSERT/UPDATE/DELETE/(MERGE)
よく利用するシチュエーション
INSERT後の IDENTITY列の値取得!
複数件の INSERT でもOK
プログラムからだと、ExecuteScalar / ExecuteReader 等の結果セットを返すメソッドで
![Page 14: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/14.jpg)
![Page 15: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/15.jpg)
WITH CTE
CTE = Common Table Expression
一時的な結果セットに名前を付けたもの
CTE は複数作成出来る
同じクエリ内で複数回参照可能
UNION ALL で自己参照 (再帰CTE)
自己結合による階層構造や連番作成 等に
![Page 16: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/16.jpg)
![Page 17: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/17.jpg)
![Page 18: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/18.jpg)
TOP
2000 でもあったが構文が変わった!
TOP <integer> => TOP (<expression>)
式が書けるようになったので、変数 / サブクエリも可
旧構文は SELECT でのみ互換性のため残してある
ORDER BY 後に適用
![Page 19: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/19.jpg)
![Page 20: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/20.jpg)
OVER 句
ウィンドウ 関数が使えるように!
順位付け関数 / 集計関数 に partition by で関数を実行するグループを決める
RANK, ROW_NUMBER, …
SUM, MAX, MIN, COUNT, …
![Page 21: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/21.jpg)
GROUP BY と PARTITON BY の違い
GROUP BY
集計関数で使用。結果セット全体を指定したグループで集計する。
結果セットもグループ単位になる
PARTITON BY
集計関数 / 順位付け関数 / (分析関数) で指定したグループで関数を実行する。
結果セットはグループ単位にならず、全件返る
![Page 22: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/22.jpg)
![Page 23: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/23.jpg)
SQL Server 2008
![Page 24: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/24.jpg)
データ型
FILESTREAM
非構造型データをファイルシステムに保存
日付/時刻
date/time/dateitme2/datetimeoffset
Spatial Data(空間データ)
geometry/geography
hierarchyid
階層構造を表す専用の型
![Page 25: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/25.jpg)
データの変更監視
変更データキャプチャ(CDC)
Enterprise のみ (Developer ではOK)
変更されたデータも保持
大きいストアドとかのデバッグで重宝!
変更の追跡 (Change Tracking)
Express Edition でも使える
変更された事実だけを保持
変更されたデータは保持しない
![Page 26: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/26.jpg)
![Page 27: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/27.jpg)
T-SQL の強化
DECLARE 時の代入
変数宣言時に代入可能
declare @hoge int = 5
複合演算子
+= -= *= /= %= ^= |=
![Page 28: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/28.jpg)
MERGE
更新対象テーブルと元となるテーブルの結合結果に対して、更新処理を行う
MATCHED
結合結果でデータが存在していた
NOT MATCHED (BY TARGET)
更新対象にデータが無かった
NOT MATCHED BY SOURCE
元となるテーブルにデータが無かった
![Page 29: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/29.jpg)
![Page 30: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/30.jpg)
![Page 31: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/31.jpg)
テーブル値コンストラクタ
行の値式のセット を表す
複数個まとめて INSERT 出来るアレの事
INSERT ~ VALUES (~), (~), (~), …
FROM や MERGE USING で利用可能
![Page 32: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/32.jpg)
![Page 33: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/33.jpg)
GROUPING の強化(GROUP BY)
GROUPING SETS
UNION ALL と同じ結果
ROLLUP
ROLLUP (C1, C2, C3)
C1, C2, C3 | C1, C2 | C1 | () の4パターン のUNION ALL
CUBE
CUBE (C1, C2, C3)全組み合わせ 8パターン の UNION ALL
(3C3 + 3C2 + 3C1 + 3C0) = 8
![Page 34: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/34.jpg)
![Page 35: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/35.jpg)
![Page 36: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/36.jpg)
SQL Server 2012
![Page 37: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/37.jpg)
OFFSET FETCH 句
結果セットから一部のデータだけをフェッチするオプション
ページングの実装用が主な使い道
ORDER BY は必須
TOP と同時には使えない
![Page 38: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/38.jpg)
![Page 39: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/39.jpg)
![Page 40: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/40.jpg)
OVER
分析関数も使えるように!
LAG
LEAD
FIRST_VALUE
LAST_VALUE
…
![Page 41: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/41.jpg)
![Page 42: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/42.jpg)
関数の追加
PARSE
文字列 => 日付/時刻 or 数値
.NET に依存 (culture 指定可)
TRY_~
PARSE / CONVERT / CAST
変換失敗で null
FORMAT
書式指定文字列を返す
.NET に依存 (culture 指定可)
![Page 43: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/43.jpg)
関数の追加
CHOOSE
値の一覧から index にある項目を返す
CHOOSE(index, val1, val2, …)
IIF
CASE の簡略化
CONCAT
N個の文字列結合で、null は空文字扱い
EOMONTH
月の最終日を返す
![Page 44: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/44.jpg)
![Page 45: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/45.jpg)
![Page 46: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/46.jpg)
SQL Server 2014
![Page 47: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/47.jpg)
なし!!
![Page 48: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/48.jpg)
SQL Server 2016Azure SQL Database
![Page 49: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/49.jpg)
Live Query Statistics
実行中のクエリの進行状況を表示する
SSMS クエリエディタ / 利用状況モニター で確認
![Page 50: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/50.jpg)
![Page 51: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/51.jpg)
Query Store
名前の通りクエリの実行情報を保持
時系列でクエリの情報を取得
同一クエリで実行プランが変わっても、プラン単位で実行情報を取得可能
特定の実行プランを強制することも
![Page 52: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/52.jpg)
Stretch Database
テーブルのデータを SQL DB に自動的に移動
テーブル検索時に、透過的に SQL DB のテーブルもリモートクエリにより検索
どっちにデータがあるか意識しなくてOK
フィルター述語により、移行対象のデータをフィルター可
SQL DB に移動したデータは変更不可
制限も色々と
![Page 53: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/53.jpg)
JSON サポート
SQLの実行結果をJSONとして出力
FOR JSON
JSON を テーブルっぽく展開する
OPENJSON
JSON 型が追加されたわけではない
文字列データ型に JSON を格納
他にも JSON 関数が追加
![Page 54: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/54.jpg)
![Page 55: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/55.jpg)
![Page 56: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/56.jpg)
Temporal Tables
ANSI SQL:2011 規格
テーブルの変更履歴を自動で取得、任意のタイミング/期間のデータを検索可能
履歴テーブルに更新データを保持
期間指定の検索は透過的に行われる
通常の運用では履歴は過去のみ
来月社員増えるから先に登録して、来月から参照可能みたいなのは無理ー
![Page 57: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/57.jpg)
![Page 58: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/58.jpg)
![Page 59: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/59.jpg)
Row-Level Security
テーブルにフィルターを設定することで、条件にマッチするデータのみ取得
INSERT / UPDATE / DELETE も制限可
ルールに一致しないデータの登録NG
INSERT / UPDATE
ルールに一致しないデータの操作NG
UPDATE / DELETE
![Page 60: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/60.jpg)
![Page 61: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/61.jpg)
![Page 62: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/62.jpg)
Dynamic Data Masking
特定の列のデータを取得時に透過的にマスキングした結果を返す
データ自体はマスキングされていない
権限持ってるユーザーはマスクされていないデータを取得可能
よくあるのは メールアドレス や クレジットカード番号 のマスキングとか
![Page 63: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/63.jpg)
![Page 64: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/64.jpg)
関数
SESSION_CONTEXT
COMPRESS / DECOMPRESS
gzip での圧縮 / 展開
DATEDIFF_BIG
AT TIME ZONE
STRING_SPLIT
STRING_ESCAPE
FORMATMESSAGE の強化
![Page 65: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/65.jpg)
![Page 66: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/66.jpg)
![Page 67: 開発者の方向けの Sql server(db) t sql 振り返り](https://reader033.fdocument.pub/reader033/viewer/2022061307/58ad19741a28ab50728b4ecf/html5/thumbnails/67.jpg)
まとめ
Standard 環境に絞ってもこれなので、Enterprise な人は大変ですね!!
どの RDBMS も似たり寄ったりの機能 / 構文 があると思うので、キーワード憶えておけば探せると思います。
クエリ書く人視点では、行式サポートしてないところがイケてない。
where ([受注番号], [受注枝番]) in (('JU0001', '01'), ('JU0001', '02')
)