SQL Serverで一時テーブルを作成する

今回はSQL Serverで一時テーブルを作成してみることにします。
ご興味がある方は、よろしければお読みいただければと思います。

一時テーブルとは？

一時テーブルとは、名前の通りある一定期間の間だけ有効なテーブルのことです。通常のテーブルは永続的にデータを保持しますが、一時テーブルは一時的にデータを保持したいときに使用します。また、通常のテーブルと同様にインデックスを付与することができたりするのも特徴です。

メモリで処理するには大きすぎたり、プログラムを組むのが大変だったりする場合には、一時テーブルを作成し処理に必要なデータを投入した上でsqlを使い簡易的に処理するなどが考えられると思います。実装の幅が広がるので、とても便利な機能です。

一時テーブルは2種類ありますので、それぞれ紹介していきたいと思います。

ローカル一時テーブルの作成方法

まずは、一時テーブルの種類の1つであるローカル一時テーブルを作成する方法を紹介します。ローカル一時テーブルの作成方法は、通常のテーブルとほぼ同じです。以下のような構文を使用して作成することができます。
少し分かりづらいのですが、一時テーブルの作成時はテーブル名の先頭に「#」（シャープ）付けることで作成されます。

CREATE TABLE #ローカル一時テーブル名 (
列名1 データ型,
列名2 データ型,
・・・
)

グローバル一時テーブルの作成方法

次にグローバル一時テーブルを作成する方法を紹介します。グローバル一時テーブルは、ローカル一時テーブルの作成方法とほぼ同じです。
今度はテーブル名の先頭に「##」（シャープ）2個を付けることで作成されます。

CREATE TABLE ##グローバル一時テーブル名 (
列名1 データ型,
列名2 データ型,
・・・
)

ローカル一時テーブルを実際に作ってみる

それでは実際に一時テーブルを作成しみます。今回は代表として、ローカル一時テーブルを作成してみます。グローバル一時テーブルとローカル一時テーブルは、有効期間を除いて作成の仕方や使い方はほぼ同じですのでグローバル一時テーブルの作成例は割愛します。

以下のように、IDと名前を持つ簡易な構成でローカル一時テーブルを作成してみます。

CREATE TABLE #local_table1 (
	id int PRIMARY KEY,
	name varchar(20)
);

すると、SSMSで確認してみたところシステムデータベースのtempdbの一時テーブル欄に上記のテーブルが作成されています。

ローカル一時テーブル

作成したローカル一時テーブルは通常のテーブルのようにデータを登録して、検索することができます。

ローカル一時テーブルにデータ登録と検索

それでは、ローカル一時テーブルとグローバル一時テーブルで何が異なるかを次で紹介します。

ローカル一時テーブルとグローバル一時テーブルの違い

それでは、簡単に両者の違いを以下に示します。

ローカル一時テーブル

ローカル一時テーブルは、作成したユーザーのみ使用できるテーブルとなります。そのため、あるユーザーAが作成した場合に他ユーザーBはユーザーAが作成したローカル一時テーブルにはアクセスすることができません。

また、ローカル一時テーブルはユーザーがセッションを切断すると自動的に削除されます。

グローバル一時テーブル

グローバル一時テーブルはローカル一時テーブルの上記制限を緩和していて、あるユーザーAが作成した場合でも他ユーザーBも使用できます。

グローバル一時テーブルは、参照するすべてのユーザーのセッションが切断されたときに削除されます。具体的には、グローバル一時テーブルを作成したセッションが終了し、そのグローバル一時テーブルを参照していた最後のTransact-SQLステートメントの完了時に削除されます。

補足

最後にちょっと補足します。ローカル一時テーブルは作成したユーザーのみ使用できるテーブルとなるのは上記で述べた通りですが、そのような性質上同時にローカル一時テーブルを作成しても特に問題にはなりません。テーブル名は同じですが、SQL Server側で連番を付与してそれぞれ別々のセッションで作成したものとして管理してくれます。

反対にグローバル一時テーブルはすべてのユーザーで使用できる性質を持っているため、同時に作成することはできません。もし、作成しようとした場合は後に実行された方はエラーとなります。

おわりに

いかがでしたでしょうか。一時テーブルを普段業務で使用している人たちは当たりまえのようなことばかりですが、知らない人からするとそんなものを作れるのかと驚くのではないかと思います。

今回はこの辺で失礼いたします。最後までお読みいただきありがとうございました。

参考

docs.microsoft.com

SQL Serverの統計情報は自動更新される？

SQL Serverで統計情報関連の情報をあさっていたところ、どうやらSQL Serverでは統計情報の自動更新という機能があるようでした。

自動更新自体は、データベースの設定によって決まるようです。
SSMSでその設定状況は、簡単に確認することができます。

[オブジェクトエクスプローラー]のデータベースを右クリック - 「プロパティ」で、ページの選択欄で「オプション」を選択する。
するとその設定項目の中に「自動」欄がありますが、そこの「統計の自動更新」が言葉通り、統計情報の自動更新の設定となるようです。
もちろん、"True"であれば統計情報は自動更新され、"False"であれば自動更新はされません。
※因みにデフォルトでは、自動更新される設定となっています

SSMSで統計情報の自動更新の設定状況を確認する

てっきり統計情報はユーザーが手動で更新するものとばかり思っていたので、他のRDBMSは知りませんがSQL Server便利だなぁと思いました。
ただし、自動更新に完全に任せっきりというわけにもいかないようです。

それについては、少々古い記事ですがMicrosoftのブログにそれについて言及したものがありました。
docs.microsoft.com

意訳すると、「統計情報の自動更新がONに設定されている場合でも、明示的に統計情報を更新する必要があることもある」とのことでした。

なので場合によっては、統計情報を手動で更新しないと古い統計情報を参照したクエリが急に遅くなってしまうこともありそうですね。
そのため、事前に手動で統計情報を更新するタイミングやその方法について確認しておく必要がありそうです。

今回は簡単ですがこれで終わります。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

SQL Serverのバージョンと製品名の対応について

SQL Serverのバージョンは「SERVERPROPERTY('ProductVersion')」を実行することで確認が可能ですが、そこで確認できる値は「15.0.2000.5」のような形式になっています。

バージョン情報確認結果

なにやらたくさん数値がありますが、値が"15"から始まるのでこれは「SQL Server 2015」なのかと思いがちですが、実はこれが違います。

実は上記の「15.0.2000.5」の製品名は、「SQL Server 2019」なのです。

上記の対応はネットで調べればすぐにわかるかと思いますが、毎回調べるのも面倒ですのでその対応がどこかのサイトにまとまってないかなということで検索しました。
公式ページにその対応が記載されている箇所を見つけましたので、備忘としてここに残します。

以下のページの「製品のバージョンを製品名に対応させる方法を教えてください。」にその記載があります。
docs.microsoft.com

以下がその内容部分を抜粋したキャプチャになります。

バージョンと製品名の対応キャプチャ

ややこしいですが、こればっかりは仕方ないので上記の通り読み解くしかないですね。
今日は簡単ですが、ここまで。最後までお読みいただきありがとうございました。

SQL Serverのバージョンの確認方法について

今回はタイトル通りですが、SQL Serverでバージョンを確認する方法について記載したいと思います。

早速ですが、やり方はとても簡単で以下のコマンドを入力するだけです。

SELECT  
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel;

私の環境で実行してみたところ、以下のように表示されました。

バージョン情報確認結果

SERVERPROPERTYと呼ばれる組み込みの関数を使って、バージョン情報やエディションなどを取得できます。
引数を変えることによって、他にもたくさんのサーバーインスタンスの情報を取得することができます。

詳細は、以下公式のマニュアルをご参照いただければと思います。
docs.microsoft.com

また、上記のSERVERPROPERTYを使う方法以外にも、以下のコマンドを実行することでもバージョン情報を取得することができます。

SELECT @@VERSION;

こちらは一つの文字列としてSERVERPROPERTYでも取得したようなエディションやバージョン情報を一度に取得できますね。

バージョン情報確認結果②

こちらも詳細については、以下公式のマニュアルをご参照いただければと思います。
docs.microsoft.com

今回はここまで。最後までお読みいただきありがとうございました。

Javaのシャロ―コピーとディープコピーについて

タイトル通り、Javaのシャロ―コピーとディープコピーについて簡単にお話していきたいと思います。

シャロ―コピー（Shallow Copy）とは？

シャロ―コピーとは英訳すると、「浅いコピー」となります。簡単にJavaのコードで説明したいと思います。

以下のようなUserクラスを用意します。

public class User {
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

上記のUserクラスを以下のようにSampleクラスで使用して、実行してみます。

public class Sample {
    public static void main(String[] args) {
        User user1 = new User();
        user1.setId(1);
        user1.setName("たろう");
        System.out.println("user1のid = " + user1.getId());
        System.out.println("user1のname = " + user1.getName());

        User user2 = user1;
        user2.setName("おさむ");
        System.out.println("user1のid = " + user1.getId());
        System.out.println("user1のname = " + user1.getName());
        System.out.println("user2のid = " + user2.getId());
        System.out.println("user2のname = " + user2.getName());
    }
}

出力結果は以下のようになると思います。
user2のnameを変更したらuser2のnameはもちろん、user1のnameも変更されてしまいました。

user1のid = 1
user1のname = たろう
user1のid = 1
user1のname = おさむ
user2のid = 1
user2のname = おさむ

以下のコードでuser2にuser1の参照している場所を代入することで、
user1とuser2はそれぞれ同じオブジェクトを指していることになります。

User user2 = user1;

これがシャロ―コピーと呼ばれるものです。しかし、場合によっては別のUserを作成してそれぞれ別のオブジェクトで扱いたい場合ももちろんあると思います。その場合に使うのが「ディープコピー」と呼ばれるものです。

ディープコピー（Deep Copy）とは？

シャロ―コピーとは英訳すると、「深いコピー」となります。こちらも簡単にJavaのコードで説明したいと思います。

Userクラスはシャロ―コピーで説明したものを使用します。Sampleクラスのみ以下のように変更します。

public class Sample {
    public static void main(String[] args) {
        User user1 = new User();
        user1.setId(1);
        user1.setName("たろう");
        System.out.println("user1のid = " + user1.getId());
        System.out.println("user1のname = " + user1.getName());

        //User user2 = user1;
        User user2 = new User();
        BeanUtils.copyProperties(user2, user1);
        user2.setName("おさむ");
        System.out.println("user1のid = " + user1.getId());
        System.out.println("user1のname = " + user1.getName());
        System.out.println("user2のid = " + user2.getId());
        System.out.println("user2のname = " + user2.getName());
    }
}

出力結果は以下のようになると思います。
今回は、user2のnameを変更してもuser1のnameは変更されませんでした。

user1のid = 1
user1のname = たろう
user1のid = 1
user1のname = たろう
user2のid = 1
user2のname = おさむ

上記ディープコピーの処理は以下のところです。今回は便利なライブラリの力をお借りしています。

        User user2 = new User();
        BeanUtils.copyProperties(user2, user1);

ライブラリの細かい説明は省きますが、上記の処理によりディープコピーを行うと、まったく別のオブジェクトとしてコピーされることになります。

以下に、一応ライブラリのリンクを張っておきます。
BeanUtils (Apache Commons BeanUtils 1.9.4 API)

おわりに

いかがでしょうか。シャロ―コピーとディープコピーについて簡単ですが、説明させていただきました。Javaのオブジェクトの参照の部分は基本的なところではありますが、しっかり理解できていないとバグになりうる問題ですので、気を付けて実装していきたいところです。

今回はこの辺で失礼いたします。最後までお読みいただきありがとうございました。

SQL Serverのbcpでテキストデータのインポート時に文字コードを指定する

以前、bcpコマンドの簡単な使い方を記事にして書いていたので、最近これを参照しながらcsvファイルをインポートしてみたのですがエラーになりました。
※以下は以前記事にしたbcpコマンドの簡単な使い方
mr-star.hatenablog.com

中途半端にデータがコピーされる感じで、中身見てみたらどうやら文字化けしているようです。
取り込みファイルは、「UTF-8」で作成されていたのでそれをコマンドに教えてあげる必要がありそうでした。

そして実際の使用例ですが、今回のケースでは以下のようにオプションを指定してあげればOKでした。

bcp MyTable in C:\data.csv -T -c -C 65001

注目すべきは、「-C 65001」のところで、csvファイルのデータのコードページを指定しています（65001は、UTF-8です）。
これで無事CSVファイルをエラーになることなく取り込みすることができました。

今回は備忘録程度にこの辺で失礼いたします。
最後までお読みいただきありがとうございました。

SQL Serverにおけるテーブルのカラム追加の位置について

かなり久しぶりの投稿となってしまいましたが、最近知ったSQL Serverの知識をメモ程度に書きたいと思います。

あるとき、SQL Serverでテーブルにカラムの新規追加をするときに、単純に現在のテーブル定義の最後尾にカラムを追加するのではなく、任意の場所にカラムの追加をしたいと思いやり方を調べました。

なぜそのようなことをしようと思ったかというと、そのカラムを追加しようと思ったテーブルはカラム定義の最後尾に更新日時や更新者のような項目を定義していたので、その後ろに新規カラムを追加するのはなんだか嫌だなぁと思ったからです。

そして調べた結果ですが、結論としては「SQL Serverでは任意の位置にカラムの新規追加をすることができない」ようです。
正直、できて当たり前かなというか、普通に構文あるんだろうなと思っていたので少々驚きました。

もし、本当にそれを実現したい場合はテーブル定義をはじめからやり直さなければならないとのことです。
つまり、データをバックアップし、テーブルを削除し、テーブルを再定義したあと、バックアップしたデータを戻すという一連の作業が必要になります。
手軽にやろうと思っていたら、上記の手順を踏まなきゃいけないとわかり面倒だなぁと思いました。。

カラムの追加時に位置を指定できないことは分かりましたが、追加した後に位置の変更とかできないのかなとも思ったので調べてみました。
それについてはMicrosoftの公式ドキュメントに記載がありました。
まず、Transact-SQL（SQL ServerのSQL文法）では、列の順序は変更できないとのことです。ただし、SQL Server Mangement Studioがあれば一応グラフィカルにカラムの列の順序を変更できるそうです。
テーブル内の列の順序の変更 - SQL Server | Microsoft Docs

因みに、上記はSQL Serverの話ですが他のRDMSはどうなのかですが、ネットで軽く調べた限りではMySQLは位置を指定してカラム追加を行えるようです。他、OracleやPostgreSQLなどはできなさそうなことが書いてありました。このことから、今回のようなテーブルのカラム追加の位置を指定するような要望は少ないのでしょうか。

かなりつらつらと書いてしまいましたが、今回はこの辺で失礼いたします。
最後までお読みいただきありがとうございました。