はてなブックマーク

SQL Server が使用しているポートを確認する方法 0 0 SQL Server が使用している TCP/IP ポートを確認する SQL Server のデータベースエンジンが使用する TCP/IP ポート番号は、デフォルトでは以下のようになっています。 デフォルトインスタンス： 1433 名前付きインスタンス： ダイナミックポート ですが、使用するポート番号は変更・固定可能なので、デフォルトインスタンスでも 1433 でない可能性もあります。 今回は、TCP/IP プロトコルが有効化されている前提で、SQL Server が使用しているポートを確認する方法をご紹介します。 SQL Server 構成マネージャーで使用しているポートを確認する方法 こちらは私の PC の SQL Server 構成マネージャーですが、今回はデフォルトインスタンスの SQL Server(MSSQLSE

前回　「 INSERT と SELECT を使って他のテーブルからデータを挿入する　」　では、 INSERT と　SELECT を使って、クエリーの結果セットを元に、テーブルにデータを挿入しました。 今回は UPDATE 文を使って、テーブルのデータを更新してみましょう。 これらのテーブルを使いますので、こちら のスクリプトを実行して、テーブルを再生成しておいてください。 UPDATE 文でテーブルのデータを更新する 一番シンプルな UPDATE 文の構文は次のようなものになります。 UPDATE [テーブル1] SET [カラム1] = [値1], [カラム2] = [値1], ... WHERE [条件]; 次のように書いても同じ意味です。 AS を使って T という、エイリアス（別名）を指定しています。 複数のテーブルを結合して更新したい時にはこちらを使います。 UPDATE T S

前回は 「 よく使われる日付と時刻の関数 2 - DATEADD, DATEDIFF, ISDATE 」 で、 よく使われる日付と時刻の組み込み文字列関数のうち、DATEADD, DATEDIFF, ISDATE を使ってみました。 次は、便利な順位付け関数を見ていきましょう。 こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。 ROW_NUMBER ファンクション ROW_NUMBER ファンクションは、結果セットにシーケンス番号を振ってくれる関数です。 構文は次の通りです。 ROW_NUMBER ( ) OVER ( [ PARTITION BY [パティションカラム 1 ], [パティションカラム 2], ... ] ORDER BY [ソートカラム 1], [ソートカラム 2], ...

SQL Server で意図的にエラーを起こすには？ 予期せぬエラーが起こった際のログ機能などとテストするために、意図的にエラーを起こしたい時や、想定と違ったデータが保存されそうになった時に、エラーを起こしてでも処理をロールバックさせたいような状況、なくはないですよね。 今回はそんな時に使える、SQL Server で意図的にエラーを起こす方法をご紹介します。 RAISERROR を使ってエラーを起こす RAISERROR を使ってシンプルにエラーを起こすクエリーは以下の通りです。 RAISERROR ([error_message], [severity], [state]); 例えば、severity 16 で、Error!!! というエラーメッセージでエラーを起こしたい場合は次のようにします。 エラーの内容については 「 SQL Server のエラーメッセージを理解する 」 をご覧

SQL Server 「データベースは使用中なので、排他アクセスを獲得できませんでした」 で復元の失敗時の対処方法 「データベースは使用中なので、排他アクセスを獲得できませんでした」 で復元の失敗 バックアップファイルからデータベースをリストアしようとした時に 「 データベース * の復元に失敗しました。 」 「 データベースは使用中なので、排他アクセスを獲得できませんでした。 」 のようにエラーが出て、復元できない時があると思います。 今回はデータベースが使用中で復元を失敗した時の対処方法です。 利用状況モニター（Activity Monitor） で利用状況を確認する 利用状況モニターを見ると、復元しようとしているデータベースを利用しているプロセスなどを確認することができます。 SQL Server Management Studio の オブジェクトエクスプローラでサーバーを右クリッ

Microsoft SQL Server 初心者の方向けに、SQL Server の紹介やインストール・接続方法等を簡単に説明します。 Microsoft SQL Server とは？ Microsoft SQL Server をインストールする SQL Server Management Studio (SSMS) をインストールする SQL Server Management Studio (SSMS) でデータベースに接続する SQL Server Management Studio - データベースを作成する SQL Server Management Studio - テーブルを作成する Microsoft SQL Server のプライマリーキー（主キー）とは？ SQL Server Management Studio - データを挿入・変更・削除する SQL Server M

前回 「 Microsoft SQL Server とは？ 」 で、ざっくり SQL Server のエディションについてご紹介しました。 本サイトでは Express エディションでサポートしていない機能も少し取り上げていますが、ほとんどは Express エディションでカバーされています。 ですので、ここでは学習者向けとされている Express エディションのすごく簡単なインストール方法を順を追ってご紹介したいと思います。 この記事を書いている時点のの最新バージョンは Microsoft SQL Server 2016 SP1 なので Microsoft SQL Server 2016 SP1 の Express エディションをインストールします。 ※ Enterprise と同じ機能を使いたい方はマイクロソフトの　Visual Studio Dev Essentials という無償

BLOB (Binary Large Object ) と FILESTREAM SQL Server ベースのアプリケーションでファイルを保存する際には二通りの方法があります。 ひとつは BLOB を通常の varbinary(max) データとしてテーブルに保存する方法、もうひとつは FILESTREAM を使って、 varbinary(max) オブジェクトをファイルシステムに保存する方法です。 どちらを使った方が良いかは、ファイルのサイズや利用方法によって違ってきますが、マイクロソフトによると、保存するファイルのサイズが 1 MB より大きく、高速な読み込みが必要な場合は FILESTREAM の利用が推奨されています。 保存するファイルのサイズが小さいことがわかっている場合は、BLOB としてデータベースに格納するほうがパフォーマンスが良いそうです。 今回は FILESTREAM

SQL Server: データベースのフル（完全）バックアップを違うデータベースにリストアする 前回は 「データベースのフル（完全）バックアップをとる方法」 をご紹介しました。 ここでは、フル（完全）バックアップをファイルから、違うデータベースにリストアする方法をご紹介します。 Management Studio から SQL Server のフルバックアップをリストアする方法 Test という名前のデータベースのバックアップを Test2 というデータベースにリストアしてみましょう。 1. まず、Database を右クリックをし、Restore Database を選択します。 2. Restore Database の画面が開きます。今回は bak ファイルからリストアするので、General ページの Source に Device を選択し、Add ボタンをクリックしてロケーショ

SQL でスリープ(sleep)させる たま～に、ストアドプロシジャー内で一定期間、スリープ(sleep)させたいような時ありませんか？ C# で言えば、Thread.Sleep メソッドのような感じです。 ここでは、そんな時に使える、フロー制御言語をご紹介します。 フロー制御言語 - WAITFOR ストアードプロシジャー内などで、一定期間、実行をブロックさせたい時には、フロー制御言語の WAITFOR を使います。 例えば、5 秒間ブロックしたい時は、WAITFOR DELAY を使って以下のように指定します。 DELAY の後には、DateTime 型の時間の部分のみを指定します。 日付の部分まで含めると、エラーになってしまいます。 最大 24 時間まで指定することができます。 他にも、WAITFOR TIME を使うことによって、指定の時間まで、実行をブロックすることができます。

SQL Server: データベースのフル（完全）バックアップ 定期的にデータベースのバックアップをとったり、大きなデータ変更の前にデータベースのバックアップをとったりしておくのは、何かあった時の為に大事なことですよね。 ここでは、手動での簡単なフル（完全）バックアップのとりかたをご紹介します。 Management Studio から SQL Server のフルバックアップをとる方法 1. まず、バックアップしたいデータベースを選択して、右クリックをし、Task > Back Up を選択します。 2. Back Up Database の画面が開くので、選択されている Databaseが 正しいか確認します。 Backup Typeは今回はフルバックアップをとりたいので Fullになっているか確認します。 Destination には前回バックアップをとったロケーションとファイル名が

キーワードでストアドプロシージャ　(stored procedure) などを検索するには？ キーワードでストアードプロシジャーやファンクションなど検索したいような時ありますよね。 いろんな方法があるかもしれませんが、これは私が仕事中によく使うクエリーのひとつです。 キーワードでストアドプロシージャなどを検索するクエリー SELECT   O.type, O.name, M.definition FROM     sys.sql_modules AS M INNER JOIN sys.objects AS O ON M.object_id = O.object_id WHERE    M.definition LIKE '%キーワード%' ORDER BY O.type, O.name; sys.objects の type はモジュールの型、 sys.objects の name はモジ

テンポラリテーブル（一時テーブル）とは？ テンポラリーテーブルはその名の通り、一時的に作って参照できるテーブルで、名前が # から始まるローカルテンポラリテーブルと、名前が ## から始まるグローバルテンポラリテーブルがあります。 テーブルの作り方は普通のテーブルと同じで、tempdb に作られます。 ローカルテンポラリテーブル（一時テーブル）は、現在のセッション上のみから参照可能で、グローバルテンポラリテーブルはどのセッションからでも参照可能です。 例えば、ストアドプロシジャーA でローカル一時テーブルを作り、AがストアドプロシジャーBを実行するような場合、B 内でAで作ったローカル一時テーブルでも参照可能です。 ※ 呼ばれた側（B 内）で作ったローカル一時テーブルは A で参照することはできません。 ローカル一時テーブルの場合、同時に上記のストアドプロシジャーA が複数のセッションから

日付型のデータを SQL Server 側でフォーマットされた文字列に変換したい時ありますよね。 その度に、フォーマットする為の Date and Time の　style、何番だったかな？と思ってしまうので、ここに私が良く使う、日付をフォーマットされた文字列に変換する方法を書いておこうと思います。 CONVERT を使って日付をフォーマットして文字列に変換する 一番良く使うのが CONVERT を使って、日付を文字列に変換する方法です。 シンタックスは CONVERT ( data_type [ ( length ) ] , expression, style) で、 expression に変換したい日付が、 style には番号が入ります。 私がよく使う、CONVERT を使った日付を文字列に変換するクエリーは以下の通りです。 DECLARE @Date DATETIME = GET

クエリーでの重複データ削除する際のコツをご紹介 データベースの設計していると、このテーブルの中で、 「このカラムと、このカラムと、このカラムと、このカラムと、このカラムの値のコンビネーションはユニークになる前提。」 というような、キーとなるカラム達が出てきたりしますよね。 わかっているならば、UNIQUE 制約をつけておくべきだと思います。 想定外のデータをインサートしようとした時点でエラーが出るので、保存されているデータは常に想定の状況になっていることが保証されていて、不具合が起こりにくくなるのは明らかです。 ここでは UNIQUE 制約をつけてない状況で、想定外の重複データができてしまった場合に、1 つを除いて削除する時のコツをご紹介したいと思います。 ROW_NUMBER() を使って、残したいデータを特定しよう 重複データでどのデータを残すか特定する際に便利なのが ROW_NUMB

SQL で改行コードを削除するには？ 値に望まない改行コードが入ってしまって、アプリケーションが想定どおりに動かなかったりすることありませんか？ ここでは、SQL での改行コードの削除方法についてご紹介します。 SQL 内の改行コードについて まず、SQL で改行コードを指定する際には CHAR 関数を使って、以下の ASCII コードを文字に変換して使用します。 CHAR(13) - CR: キャリッジリターン (carriage return) CHAR(10) - LF: ラインフィード (line feed) SQL で改行コードが入っているレコードを探す カラムの値に改行コードが入っているレコードを探してみましょう。 テキストエディターや OS などによって、改行コードは LF のみであったり、CR+LF であったりしますよね。 ここでは、 CR+CF が入っている前提で確認して

断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法 断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) するには？ インデックスが断片化してしまうと、クエリーのパフォーマンスが低下してきますよね。 インデックスの再構成 (Reorganize) や再構築 (Rebuild)を行うと、断片化が解消されますので、定期的にインデックスの再構成 (Reorganize) や再構築 (Rebuild) をすると良いですね。 ですが、やみくもにデータベース内の全てのインデックスを再構成 (Reorganize) や再構築 (Rebuild) すると、データベースが大きくなればなるほど、実行にすごく時間がかかってしまったり、ログファイルが大きくなりすぎてしまっ

SQL Server 入門サイト このサイトでは、SQL Server や T-SQL を初めて学ぶ人のために、SQL Server に関する基本的なことから、便利な T-SQL ＆クエリーの紹介や、チューニングの仕方などについて解説しています。

データベース内の全テーブルのサイズ（ディスク使用量）を取得するには？ データベースサーバの空き容量が少なくなってきて、どのテーブルがディスク容量をたくさん使っているのかな？と調べたい時ありますよね。 ここでは、そんな時に使えるクエリーをご紹介します。 テーブルのサイズ（ディスク使用量）を取得するには テーブルのサイズ（ディスク使用量）を簡単に取得するには sp_spaceused というシステム・ストアド・プロシジャーを使って確認することができます。 この sp_spaceused というストアド・プロシジャーは、パラメターを渡さなければデータベースのサイズを、テーブルなどのオブジェクト名を渡すと、そのオブジェクトのサイズ情報を返してくれます。 例えば、Person.Person テーブルのサイズを取得するクエリーは以下の通りです。 データベースを選択してから実行して下さい。 rows:

SQL Server や T-SQL を初めて学ぶ人のための学習サイトです。 便利なクエリーやチューニングの仕方なども掲載しています。

カラムの値からカンマ区切り (CSV) の文字列を生成するには？ SQL のスクリプトで、複数行のカラムの値から、1 つのカンマ区切り (CSV) の文字列を生成したいような時ありますよね。 例えば以下のような感じです。 FOR XML 句を使って、カラムの値からカンマ区切り (CSV) の文字列を生成する方法 カンマ区切り (CSV) の文字列を生成する方法はいろいろありますが、今回は FOR XML 句を使ったやり方のひとつをご紹介します。 FOR XML 句 を SQL クエリーに指定すると、SQL クエリーの結果を XML 形式で取得することができます。 では実際に、カンマ区切り (CSV) の文字列を作ってみましょう。 例えば、以下のような Groups　マスターがあったとします。 そして、誰がそのグループに属するかが保存されている、以下のような GroupMembers テーブ

SQL Server へのリンクサーバ (Linked Server) を作成する このページでは SQL Server へのリンクサーバ (Linked Server) の設定方法をご紹介します。 リンクサーバ (Linked Server) の作り方 まず、リンクサーバ (Linked Server) を作るには、 sp_addlinkedserver というシステムストアードプロシジャーを使います。 sp_addlinkedserver の構文は以下の通りです。 sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ] [ , [ @locati

CSV ファイルを SQL Server にインポート アプリケーションを開発していると、ファイルから SQL Server にデータをインポートしなければならないことがあります。 ここでは CSV ファイルを BULK INSERT を使って SQL Server のテーブルにインポートする一番シンプルな方法をご紹介します。 まずデータを保存するテーブルを作りましょう。 以下のクエリーを実行して、Students という名前のテーブルを作ります。 CREATE TABLE Students ( StudentID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL, BirthDay DATE NULL, Gender CHAR(1) NULL ); インポートしたい CSV ファ