sql-server

SQL Server - シーケンスを使う

SQL Serverでシーケンスを使うサンプルです。 シーケンスを久しぶりに使おうと思ったらあまり覚えておらず、以前に試したので記事が残ってるかと探してみましたが、見つからなかったのでさらっと残しておきます。 ざっくり言うとシーケンスは連番を生成する…

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

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

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

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

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 …

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…

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

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

T-SQL - DROP IF EXISTS

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

SqlConnectionの接続タイムアウトを確認してみる

SQL Serverで単にタイムアウトと言っても接続タイムアウトとクエリタイムアウト(コマンドタイムアウト)の2つがあります。発生するタイミングが違いますし、それぞれ理解しておきたいなと思います。 まずはちょっと簡単に整理します。 接続タイムアウト 名…

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を含んだ複合外部キーのメモ

複合外部キーを使うことになったんですが、外部キー制約を設定するカラムの1つの値がNULLの場合に想像していた動きと違ったのでメモを残しておきます。(想像と違うのはわたしだけかもですが) 次のようなテーブルがあったとします。SampleBに外部キー制約が…

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

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

Entity Framework - ワイルドカード文字を使った文字列検索

Entity FrameworkでstringのContainsメソッド、StartsWithメソッド、EndsWithメソッドを使って文字列を検索すると、LIKE演算子で「%」を使ったselect文が作成されます。 MSDNとかのドキュメントっぽいものは見つけられませんでしたが、適当なエンティティとD…

Entity Framework - SkipメソッドとTakeメソッドを使うとFETCH句とOFFSET句が使われることを確認する

SQL Server 2012からOFFSET句とFETCH句が追加されて、クエリだけでページングを実現できるようになりました。Entity Frameworkを使っているときに、SkipメソッドとTakeメソッドを使うとOFFSET句とFETCH句が使われるの?ってあたりが気になったのでログで発行…

再帰トリガーと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のざっくりした書き方はこんな…

Entity Framework - パラメータを渡してSQLを実行する

Entity Frameworkを使って直接SQLを実行したいときがあって、さらにパラメータの渡し方が気になったので試していました。 まず直接SQLを実行するメソッドは2つ用意されています。 Database.SqlQuery メソッド (System.Data.Entity) Database.ExecuteSqlComma…

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

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

SqlCommand の実行を SQL Server Profiler でトレースしてみた

SqlCommand の ExecuteReader でクエリを実行する時にパラメータを渡すと sp_executesql を使って実行されます。パラメータを使わないと sp_executesql は使われないようです。 MSDN より引用します。 ADO.NET での side-by-side 実行 .NET Framework versio…

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 - 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句にも書けることを知りました。 …

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

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