t-sql

FORMATMESSAGE関数を試す

SQL Server 2016からFORMATMESSAGE関数が拡張されて、プレイスホルダーを使って文字列を組み立てられるようになりました。 FORMATMESSAGE (Transact-SQL) | Microsoft Docs C言語のprintfみたい感じですかね。たぶん。 文字列を埋め込むには%sを使います。 -…

STRING_SPLIT関数で文字列を分割する

SQL Server 2016から使えるSTRING_SPLIT関数を試してみます。 STRING_SPLIT (Transact-SQL) | Microsoft Docs この関数は1つ目の引数の文字列を2つ目の引数の文字で分割します。戻り値はvalueカラムを1つだけ持つ表形式の結果セットです。 -- ','で分割する …

SQL Server - 拡張イベントを使ってスロークエリを取得するクエリ(ファイル編)

前の記事では、拡張イベントを使ってスロークエリを取得しつつ次の2つのクエリを書いてみました。 ターゲットにリングバッファ(メモリ)を指定してセッションを作成するクエリ リングバッファに出力されたイベントデータを確認するクエリ ichiroku11.hatena…

SQL Server - 拡張イベントを使ってスロークエリを取得するクエリ

SQL Serverの拡張イベントを手っ取り早く試すにはこの記事が良いかなと思います。 クイック スタート: SQL Server の拡張イベント この記事もわかりやすくSSMSのGUI自体もわかりやすいので、簡単に拡張イベントを使えるかなと思います。簡単に使えてしまうの…

T-SQLでn個ずつのグループにわける

前回はn個のグループにわけるクエリを書いてみました。 ichiroku11.hatenablog.jp 今回はn個ずつになるようにグルーピングするクエリをROW_NUMBER関数を使って書いてみたいと思います。 ROW_NUMBER (Transact-SQL) | Microsoft Docs 前回と同じように11から2…

T-SQLでn個のグループにわける

NTILE関数を使うと簡単にデータをn個のグループにわけることができます。正確には、n個のグループにわけた場合のグループ番号を割り振ると言ったほうがいいかもしれません。 NTILE (Transact-SQL) | Microsoft Docs NTILE関数を使ったサンプルを書いてみます…

SQL Server - 拡張イベントで収集できるイベントデータを確認するクエリ

拡張イベントで収集できるイベントデータを確認するクエリです。 動的管理ビューを3つ使います。sys.dm_xe_packages、sys.dm_xe_objects、sys.dm_xe_object_columnsの3つ。 動的管理ビュー 概要 sys.dm_xe_packages (Transact-SQL) パッケージ一覧。イベント…

SQL Server - トレースイベントに対応する拡張イベントを確認するクエリ

拡張イベントを調べています。勉強した小ネタを残していこうと思います。 今回はトレースイベントに対応する拡張イベントを確認するクエリです。sys.trace_eventsとsys.trace_xe_event_mapを使います。 sys.trace_events (Transact-SQL) trace_xe_event_map …

T-SQL - OVER句のROWS句のサンプル

OVER句のROWS句がすごいなと思ったので、少し整理していくつかサンプルを残しておきます。 OVER 句 (Transact-SQL) OVER句を使うと行をグルーピングしたり、行に順位付けをしたり、行セットの範囲を指定したりといったことができます。 OVER句の括弧の中には…

SQL Server - OPENROWSETでJSONファイルを読み込んでOPENJSON

前回、OPENJSONを使ってJSON文字列を結果セットに変換してみました。 ichiroku11.hatenablog.jp そのあとにそういえば「JSONファイル」を読み込んでOPENJSONを使う方法を知らないなと思いました。検索してみると次の記事が見つかります。 Importing JSON fil…

SQL Server - OPENJSONは役に立つ

SQL Server 2016から手軽にJSONを扱えるようになっています。 MSDNなどを読みながら勉強している最中で全体を把握しているわけではないですが、SQL ServerのJSONサポートは次の2つの機能がメインなのかなと思います。 機能 概要 FOR JSON句 結果セットをJSON…

数値や文字列を右詰して前を「0(ゼロ)」で埋める(T-SQL)

T-SQLの小ネタです。 数値や文字列を右詰して前を「0(ゼロ)」で埋めた文字列を作ります。 前回はC#でやってみたので今回はT-SQLです。 ichiroku11.hatenablog.jp 数値の右詰と0埋め 数値の場合はSQL Server 2012から追加されているFORMAT関数を使います。C…

T-SQL - BETWEENのメモ

BETWEENは自分ではあまり使わないのでたまに見かけるとあれ?含むの?含まないの?どっちだっけ?ってなります。 カップル専用アプリのことではないです。 between A and B A以上かつB以下。AもBも含む。 select * from (values(1), (2), (3), (4), (5)) as …

SQL Server - xmlデータ型のメソッド(query、value、nodes)を試す

そういえばT-SQLでxmlを扱ったことがないなーと思ったので、とりあえず基本の基本を押さえるために簡単なサンプルを書いてみましたというお話です。 まずxmlデータ型のメソッドはこれだけあります。 xml データ型のメソッド そのうちよく使うんじゃないかな…

T-SQL - DROP IF EXISTS

先週SQL Server 2016の提供が開始されました。 新機能を調べだしたところですが、まずはとりあえずDROP IF EXISTSを試してみました。 開発中、試行錯誤してたりとかでテーブル構造がなかなか固まらず、テーブルを作っては消してまた作るということを繰り返す…

SQL Server - 主キーの一覧を取得するクエリ

前回は外部キーの一覧を取得するクエリを書いてみました。 ichiroku11.hatenablog.jp 今回は外部キーのときと同じようにテーブル名やカラム名を含めて主キーの一覧を取得するクエリを書いてみました。 select i.name as [主キー名], ic.index_column_id as […

SQL Server - 外部キーの一覧を取得するクエリ

テーブル名やカラム名を含めて外部キーの一覧を取得するクエリです。探したら似たようなクエリが見つかる気もしますが練習もかねて書いてみました。また使うことがあるかなと。 select fk.name as [外部キー名], fkc.constraint_column_id as [外部キーカラ…

SQL Server - null以外の値でユニークにする

SQL Serverでnull以外の値でユニークにしたいときがあると思います。 先に方法を書くと「フィルター選択されたインデックス(filtered index)」を使うことでnull以外の値でユニークを実現できます。 フィルター選択されたインデックスの作成 ということで今…

LINQでクロス結合

クロス結合(CROSS JOIN)はLINQを使ってどうやって書くんだろうと気になったので調べてみました。 SQLでもほとんど使わないのでC#でもほとんど使わないとは思いますが・・・。重複ありのすべての組合わせを出したいときに使えるかなーと思います。まぁほと…

T-SQL - TOP句とパラメータ

SELECT文のTOP句にパラメータを使用できることを初めて知りました。 こんな感じ。Dapperでクエリを実行するサンプルです。 // TOP句に@countを指定する var sql = @" select top (@count) * from (values (N'ホルモン', 490), (N'カルビ', 880), (N'キムチ',…

再帰トリガーとRECURSIVE_TRIGGERSオプションのメモ

UPDATEトリガーで自身を定義したテーブルに対してまたUPDATEしたら無限ループに入らないの?と疑問に思ったので試してみましたと言う内容です。 説明するよりクエリを見てもらった方がわかりやすいと思うので、さっそくテーブルとUPDATEトリガーを。UPDATEト…

DMLトリガーを試す

トリガーをほぼ使ったことがないトリガー初心者なのですが、使う機会がでてきそうで調べていました。とりあえずDMLトリガーの、さらにAFTERトリガーにしぼって気になったところを試してみたので、少しメモとして残しておきます。 まずはテスト用のテーブルと…

PRINT・RAISERRORとSqlConnection.InfoMessageイベント

T-SQLのPRINTを使うとクライアントにメッセージを通知できます。そのメッセージはSqlConnection.InfoMessageイベントで取得できます。 また、RAISERRORでも重大度(severity)が10以下の場合は、同様にSqlConnection.InfoMessageイベントでメッセージを取得…

再帰CTE(共通テーブル式)で階層構造のパスを作る

検索すると再帰CTEのサンプルはいろいろ見つかりますが、自分で書いたクエリを残しておきたいなと思って書いてみました。おさらいというかメモというか。 共通テーブル式を使用する再帰クエリ 上記MSDNにもありますが、再帰CTEのざっくりした書き方はこんな…

t-sql - backup database と restore database で変数を使う

backup database や restore database で DB名のところに変数を指定できます。 DB名やテーブル名などでは変数が使えないと思っていたのでちょっとした発見でした。ということでクエリを残しておきます。 BACKUP (Transact-SQL) RESTORE (Transact-SQL) backu…

SQL Server - OFFSET 句と FETCH 句

SQL Server 2012 からですが、ORDER BY 句に OFFSET 句と FETCH 句が追加されています。クエリだけでページングを実現できるので素敵ですね。 ということで試してみたのでクエリなどを残しておきます。 まずクエリはこんな感じです。 select * from テーブル…

SQL Server - ORDER BY 句で null は最小値扱い

当たり前のことのようにそんなもんだと思っていたのですが、あらためて MSDN を読んでいるとちゃんと記述があったのでメモ。 ORDER BY 句 (Transact-SQL) 引数の ASC | DESC のところを引用しておきます。 ASC | DESC 指定した列の値を昇順と降順のどちらで…

T-SQL で「数値」と「3桁区切りの文字列」を変換する

SQL Server 2012から format 関数と parse 関数を追加されています。 FORMAT (Transact-SQL) PARSE (Transact-SQL) これらの関数を使うと、 数値を3桁区切りの文字列に変換(format) 3桁区切りの文字列を数値に変換(parse) といったことをクエリだけで行…

T-SQL - sum 関数と over 句を使って累計を求める

sum 関数と over 句を使って累計を求めてみたいと思います。 SUM (Transact-SQL) OVER 句 (Transact-SQL) とりあえずデータ。どこかにある焼き鳥屋さんの売り上げのようなテーブルだと思ってもらえると。 select * into #Sale from (values ('2013/12/09', N…

T-SQL - like 演算子でワイルドカード文字(「%」や「_」)を検索

方法は2つあるんですね。探せば出てくるとは思いますが、メモしておきます。 ワイルドカード文字を角括弧([と])で囲む escape 句を使う LIKE (Transact-SQL) いつも通りとりあえずデータを用意して。 select * into #Message from (values(N'30cm'), (N'50…