t-sql

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…

t-sql - object_id 関数で一時テーブルを指定する

テーブルの存在チェックなどで使う object_id 関数で一時テーブルを指定する場合は、 object_id('tempdb..#Test') のように書くんですね。 また一つ勉強になりました。 OBJECT_ID (Transact-SQL)より。 一時テーブル名を指定する場合は、現在のデータベース…

T-SQL - select into 文とテーブル値コンストラクタ

select into文を使うと、create table文を使わずに新しいテーブルを作成できることを知りました。 INTO 句 (Transact-SQL) SELECT INTO を使用した行の挿入 そして、テーブル値コンストラクタ(values (...)のあれ)は、from句にも書けることを知りました。 …

行が存在すればupdate、存在しなければinsertするmerge文

merge文はたまーに使う時があって、いざ書こうとすると忘れてたりします。 なので簡単な例をメモしておきます。 merge文の例 こんなテーブルがあったとして。 create table #Fruit( Name nvarchar(3), Price int, constraint PK_Fruit primary key(Name)); @…

T-SQLのトリガーで時間帯(期間)の重なりを防ぐ

タイトルが微妙で後から自分が見ても、ん?なんだっけ?ってなりそうな気も・・・。 それはさておき、会議室とかを予約するようなシステムで「誰かがある会議室のある時間帯を予約したら、他の人はその時間帯を予約できない」といった制御をSQLだけでどうす…

case式を使って条件分岐するupdate文を試す

CASE式のススメ(後編)を読んで、update文でcase式を使って条件分岐するテクニックを知ってかなり感動しました。行ごとに異なる更新を1つのupdate文でできるのすごいですね。 今回はこの記事に書いてある、主キーを入れ替えるテクニックを少し応用してみた…