MS SQL Server

MS SQL Server2000 の 管理ツール Enterprise Manager で、既に存在するデータベースのインスタンスやテーブル、ストアドプロシージャなどをスクリプト化する手順と、指定するオプションのメモ。

- なぜ SQL スクリプトを作成するのか

既存の DB から SQL スクリプトを作成するという、本来と逆の手順をとるのはなぜか。それは、DB のテーブル設計やストアドのコーディングを RDBMS 付属の GUI ツールや自動生成ツールに頼るようになり、CREATE TABLE を手で書いたりすることがなくなったからだ。

要するに、GUI のツールで開発用サーバ上の DB を直接操作しながら作って、完成した時点でテストサーバや本番サーバに配備するというスタイルで DB まわりを作るようになったので、逆の作業が必要になったということだ。

もちろん、私も初めて DB を作ったときは CREATE TABLE をテキストエディタで手書きしたものだ。その後 CREATE TABLE を自動生成するスクリプトを作ったり、したが、現在は GUI のツールを利用するというスタイルに落ち着いている。私が扱うのはテーブル数15から30程度の小規模な DB なので、それで十分用が済んでしまう。

わざわざ SQL に落とさずに、SQL Server の DTS (Data Transformation Service - データ変換サービス) で転送すれば用が済んでしまう場合もある。ただ、プレーンテキストのスクリプトにするということは、スナップショットとして保存したり、Subversion や PVCS、VSS などの汎用のバージョン管理ツールで履歴を管理できるということだ。これは大きな利点だ。SQL Server は VisualStudio + VSS と緊密に連携することもできるが、サーバの構成などによってはその方法を採れないことも多いため、スクリプト化は非常にありがたい機能だ。

- SQL スクリプト生成の手順

Enterprise Manager の データベースオブジェクトを右クリックし、SQL スクリプト生成を選択。
「SQL スクリプト生成ウインドウ」が表示されるので、対象オブジェクトやオプションを指定する。

以下、オプションのメモ。
■はチェックボックスのチェックオンを、□はオフを表す。
●はラジオボタンのチェックオンを、○はオフを表す。

- 「全般」タブ スクリプト化するオブジェクト指定

基本は全オブジェクトを SQL スクリプト化する。抜けがあると怖いので、全オブジェクトのスクリプト(O) を明示的に指定するのがよい。

■全オブジェクトのスクリプト(O)

■全テーブル(T)
■全ビュー(W)
■全ストアド プロシージャ(P)
■全デフォルト(D)
■全ルール(U)
■全ユーザー定義データ型(Y)
■全ユーザー定義関数(F)

- 「書式」タブ スクリプト書式オプション

「すべての従属オブジェクトに対してスクリプトを生成(E)」は今回必要ないのでオフにした。

スクリプト オプションを使用して、オブジェクト スクリプトを作成する方法を指定できます。

■オブジェクトごとに CREATE <object> コマンドを生成(C)
■オブジェクトごとに DROP <object> コマンドを生成(D)
□すべての従属オブジェクトに対してスクリプトを生成(E)
■スクリプト ファイルに説明用ヘッダーを含める(I)
■拡張プロパティを含める(X)
□7.0 互換の機能のみスクリプトを作成(O)

スクリプト ファイルに説明用ヘッダーを含める(I)をオンにしておくと、対象オブジェクトの名前と現在の日付をコメントとして挿入してくれる。以下のような感じ。「/****** オブジェクト : ストアド プロシージャ dbo.P_GetLandscapeCustomers スクリプト日付 : 2006/08/23 12:43:12 ******/」テキストの差分をとる場合に邪魔になる場合はオフにすればいい。

「拡張プロパティを含める」をオンにしておくと、EnterpriseManager のテーブルデザイナで付加したカラム(列)への説明コメントも含めてスクリプトを生成してくれる。

- 「オプション」タブ スクリプト作成オプション

全部チェックオン。ただし、「データベース スクリプト(C)」はデータベースファイル (.mdf) や トランザクションログファイル (.ldf) の絶対パスをハードコードしてしまう。そのため、パスが異なる環境で実行する場合はチェックをオフにするか、スクリプト中のパスを修正して整合性をとる。

■データベース スクリプト(C)
■データベース ユーザーとデータベース ロール スクリプト(S)
■SQL Server ログイン (Windows と SQL Server ログイン) スクリプト(L)
■オブジェクト レベル権限スクリプト(M)

テーブル スクリプト作成オプション
■インデックス スクリプト(X)
■フルテキスト インデックス スクリプト(I)
■トリガ スクリプト(R)
■主キー、外部キー、デフォルトと CHECK 制約スクリプト(K)

ファイル オプション
○ファイル フォーマット
○MS-DOS テキスト (OEM)(D)
○Windows テキスト (ANSI)(W)
●インターナショナル テキスト (Unicode)(U)

生成するファイル
●1 ファイルに作成(F)
○オブジェクトごとにファイル作成(B)

Gatherer プロジェクトに関する警告またはエラーというものがイベントログに記録されていた。SQL Server2000 のフルテキスト検索のクローラーのエラーの模様。

手作業で DTS (Data Transfer Service) を使ってマスターデータ編集用サーバから運用系サーバへデータ転送したあと、クローラがクローリングを開始したタイミングで発生している。

該当のファイルやイベントログなどを見てもあまり手がかりがない。Web を検索しても情報がとくに出てこない。サービスには支障が出ていないし、致命的なものではないと判断。現行のまま運用を継続することにした。

イベントの種類:    警告
イベント ソース:    Microsoft Search
イベント カテゴリ:    Gatherer
イベント ID:    3035
日付:        2005/12/26
時刻:        18:32:51
ユーザー:        N/A
コンピュータ:    sonic64
説明:
Gatherer プロジェクト <SQLServer SQL0001100005> に関する警告またはエラーで、ファイル <d:\sqldata\MSSQL\FTData\SQLServer\GatherLogs\SQL0001100005.85.gthr> に記録されたものがあります。このメッセージの詳細については、Gatherer ログ クエリ オブジェクト (gthrlog.vbs, ログ参照 Web ページ) で使用するファイルを参照してください。

ところで、Gatherer (ギャザー) っていうと、風来のシレンに出てきたあの蟹みたいなモンスターを思い出しちゃうな。あ、Gather (ギャザー) じゃなくて Gatherer (ギャザラー) か。勘違い勘違い。

SQL Server Compare は SQL Server のテーブルの相違点比較ツール。フリー。まだ試してないけど、ストアドやビューも比較できるとのこと。

以前、友達が「こんなツールない?」って質問してきてたなあ。データの確認に使いたいとか言ってた。そのときは「CSV か XML で出力して diff 取ったら?」ってアドバイスした。結局、要件がそれだけじゃ済まなかったらしく、ツールを作ったらしいけど。

Yes Software
http://www.yessoft.com/
http://www.yessoft.com/software/sqlservercompare/sqlserverco ...

SQL Server Compare is an easy-to-use tool to compare 2 SQL Server database schemas and show or save the list of differences. The program can compare database tables, stored procedures, indexes, users, triggers, views and record counts and SQL scripts of different objects can be viewed and saved. Databases can be located on separate servers, using integrated Windows security or SQL server login. The program does not change the database in any way, and it can not execute any SQL statements - it only compares the schema. Requires SQL server or client installed.

via: DB マガジン2005年12月号141ページ

SQL Server2000 をインストールしたマシンを OS ごとリブートしたところ、イベントログに The data portion of event 19002 from MSSQLSERVER is invalid. というエラーが記録されていた。

イベントの種類:    エラー
イベント ソース:    SQLSERVERAGENT
イベント カテゴリ:    Alert Engine
イベント ID:    322
日付:        2005/10/14
時刻:        17:44:28
ユーザー:        N/A
コンピュータ: SANODG
説明:
The data portion of event 19002 from MSSQLSERVER is invalid.

SNMP エージェントが悪さをしている模様。

BUG: SNMP Agent Message Causes Event ID 322
http://support.microsoft.com/default.aspx?scid=http://suppor ...

SYMPTOMS

SQL Executive causes another application event when the start or stop message event occurs from the SNMP Agent and this error text appears:
Event ID: 322
Source: SQL Executive
Description: The Data portion of event 19002(19004) from MSSQLServer is invalid.

MORE INFORMATION
The messages from the SNMP Agent are informational only and can be ignored.

とりあえず SNMP は必要だし、無視していいとのことなので現状維持のままとした。

Web サービスにリクエストを投げたときに、それがタイムアウトするまでの時間を延ばしたい。

- 大人の事情

Web サービスのタイムアウトの時間を延ばすなんて、なんでそんなことする必要があるの? と思うかもしれない。でも、大人にはいろいろ事情がある。とにかく Web サービスがタイムアウトすることだけは避けなければならない。それが今回の課題。たとえクライアントからリクエストを投げた後、レスポンスが返ってくるまで2分とかかかったとしても。

- Web サービスの背後で動いてるもの

Web サービスは単純だ。SOAP なり REST なりでリクエストを投げ、レスポンスが返ってくる。ただそれだけ。でも、普通はこれを実現するためにいくつかのミドルウェアが背後で動いている。

データストアとして、データベース。たとえば、MS SQL Server。
データベースに接続しクエリを投げるためのデータプロバイダ。.NET で MS SQL Server 相手だったら System.Data.SqlClient。
データストアから取得したデータを加工するビジネスロジック。
ビジネスロジックを載せるための Web アプリケーションフレームワーク。たとえば、ASP.NET。
REST や SOAP のリクエストを受けるための HTTP サーバ。たとえば、IIS (Internet Infomation Server)。
REST や SOAP のリクエストを投げるための HTTP クライアント。.NET だったら System.Web.Services.Protocols.SoapHttpClientProtocol あたりがいろいろやってくれる。

これらの全てのタイムアウトを延ばさないと、Web サービスのタイムアウトを延ばすという目標は達成できない。以下、それぞれのタイムアウト設定方法のメモ。

- SQL Server のクエリ実行時間タイムアウト

SqlCommand.CommandTimeout プロパティ System.Data.SqlClient 名前空間
http://www.microsoft.com/japan/msdn/library/ja/cpref/html/fr ...

プロパティ値
コマンド実行の待機時間 (秒)。既定値は 30 秒です。

解説
0 は、制限なしを示します。ただし、コマンド実行の試行が永久的に待機されるため、 CommandTimeout では使用しないでください。

今回設定が必要なもののうち、この SqlCommand.CommandTimeout が一番短い。短いといっても、30秒もあるわけだけど。

- IIS の HTTP レスポンス生成時のタイムアウト

IIS5 の場合。インターネット サービス マネージャを開き、Web サイトのプロパティを表示させる。「既定の Web サイト」しかなかったら、それを右クリックしてプロパティを表示させる。「Web サイト」タブの「接続」の「接続のタイムアウト」に秒単位で設定できる。デフォルトは900秒。

接続のタイムアウトを設定する
http://www.microsoft.com/resources/documentation/windowsserv ...

- SOAP クライアントのタイムアウト

WebClientProtocol.Timeout プロパティ
http://www.microsoft.com/japan/msdn/library/ja/cpref/html/fr ...

プロパティ値
XML Web サービスを同期的に呼び出すときのタイムアウト (ミリ秒単位)。既定値は 100000 ミリ秒です。

解説
Timeout プロパティを Timeout.Infinite に設定すると、要求がタイムアウトしないことを示します。XML Web サービス クライアントが Timeout プロパティをタイムアウトなしに設定しても、Web サーバーが要求をサーバー側でタイムアウトにする場合があります。

MS SQL Server2000 の IDENTITY の値取得についてのメモ。IDENTITY は 2003-05-13 の「MS SQL Server のシーケンス型」で書いたように、連番というか一意な ID を DB 側で振り出すための仕組み。

- 結論。IDENT_CURRENT @@IDENTITY SCOPE_IDENTITY の用途別使い分け

IDENT_CURRENT @@IDENTITY SCOPE_IDENTITY の違い について、用途別にメモ。

トリガーなどで INSERT された値も含めた特定のテーブルの値を取りたいときは IDENT_CURRENT。
トリガーなどで INSERT された値も含めて取りたいときは @@IDENTITY。
トリガーなどで INSERT された値を除いて取りたいときは SCOPE_IDENTITY。

- IDENT_CURRENT のリファレンスはわかりにくい

リファレンスの説明を読んだが非常にわかりにくい。「任意のセッション」とか「有効範囲内」とか、説明なしでいきなり使われている言葉ばかり。任意って、誰の意志が反映されてるの? 結局何を指しているのかさっぱりわからない。

Transact-SQL リファレンス IDENT_CURRENT
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_ia ...

IDENT_CURRENT は、任意のセッションと有効範囲内の特定のテーブルに対して生成された最後の ID 値を返します。
@@IDENTITY は、すべての有効範囲を対象に、現在のセッション内の任意のテーブルに対して生成された最後の ID 値を返します。
SCOPE_IDENTITY は、現在のセッションと現在の有効範囲内の任意のテーブルに対して生成された最後の ID 値を返します。

訳文よりも原文の方がわかりやすいんじゃないかと思ったので、英語版を見てみる。

Transact-SQL Reference IDENT_CURRENT
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_82i ...

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

英語で書かれた方がわかりやすい。「任意」って any だったのか。all を「全て」と訳すのはまあいいけど。

IDENT_CURRENT は引数で指定した特定のテーブルについての値。全セッション、全スコープ。
@@IDENTITY は全てのテーブル、現在のセッション、全てのスコープ。
SCOPE_IDENTITY は全てのテーブル、現在のセッション、現在のスコープ。

- Transact-SQL リファレンス IDENT_CURRENT

例があるのは嬉しいけど、やっぱりわかりにくい。例を一つ一つ追いかけるよりも、英語のドキュメントを読んだ方がわかりやすい。

IDENT_CURRENT

任意のセッションと有効範囲内の特定のテーブルに対して生成された ID 値を返します。

構文
IDENT_CURRENT('table_name')

引数
table_name

ID 値が返されるテーブルの名前です。table_name は varchar であり、既定値はありません。

戻り値の型
sql_variant

解説
IDENT_CURRENT は、MicrosoftR SQL Server? 2000 の ID 関数である SCOPE_IDENTITY と @@IDENTITY に似ています。3 つの関数とも、最後に生成された ID 値を返します。ただし、各関数の中で、"最後" が定義される有効範囲とセッションがそれぞれ異なります。

IDENT_CURRENT は、任意のセッションと有効範囲内の特定のテーブルに対して生成された最後の ID 値を返します。


@@IDENTITY は、すべての有効範囲を対象に、現在のセッション内の任意のテーブルに対して生成された最後の ID 値を返します。


SCOPE_IDENTITY は、現在のセッションと現在の有効範囲内の任意のテーブルに対して生成された最後の ID 値を返します。
例
この例では、IDENT_CURRENT、@@IDENTITY、および SCOPE_IDENTITY がそれぞれ異なる ID 値を返します。

USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
  INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT  * FROM t6
--id is empty.

SELECT  * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()
/* Returns the value 1, which was inserted by the
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action
so far in this session.*/

SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/

MS SQL Server で CONTAINS を使った全文検索について調査。2003-06-13 にも似たようなことを調べてたけど、今回はちょっと本気。

- 要件

SQL Server で全文検索機能を使いたい。形態素解析でも N-gram でもいい。全角半角文字同一視、アラビア数字と漢数字の同一視ができればいい。対象は一文書約 4KB の日本語データで、数百から二千件程度。パフォーマンスは重視しない。簡単に作れることが肝要。この要件だと、CONTAINS 述語が使えそう。

- 事前準備

2003-06-13 でも書いたように、フルテキストインデックスをあらかじめ構築しておく必要がある。

- CONTAINS の文法

Transact-SQL リファレンス CONTAINS
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_ca ...

SELECT * FROM CONTAINSTABLE(document, *, '"QUERY"');

基本。
CONTAINS または CONTAINSTABLE 述語の引数としてクエリを渡す。
クエリ全体はシングルクオートで囲む。
クエリの中の単語はダブルクオートで囲む。

複数のクエリで AND や OR を作りたいときは、ダブルクオートをで囲んだ単語群を AND や OR で連結する。

SELECT * FROM CONTAINSTABLE(document, *, '"foo" AND "bar" AND "buzz"');

あとは prexi_term と細かな機能くらいしかリファレンスに載っていない。

<prefix_term>

指定のテキストで始まる単語または語句の照合を指定します。プレフィックス語句を二重引用符 ("") で囲み、後ろの二重引用符の前にアスタリスク (*) を挿入すると、アスタリスクの前に指定された文字列で始まるすべてのテキストが照合されます。この句は、CONTAINS (column, '"text*"') のように指定します。アスタリスクは、0 または 1 つ以上の文字に一致します。その単語または語句を語根とする文字もこれに含まれます。CONTAINS (column, 'text*') のように、テキストとアスタリスクが二重引用符で区切られていないと、フルテキスト検索でアスタリスクが文字と見なされ、text* に対する完全一致が検索されます。

<prefix_term> が語句のときは、語句に含まれるそれぞれの単語が独立したプレフィックスと見なされます。したがって、"local wine *" というプレフィックスを指定しているクエリでは、"local winery"、"locally wined and dined"などの行が一致します。

記号だけで検索したい場合とか、ダブルクオートを含む単語をどうエスケープしたらいいかなどについての記述が見つからない。そもそも、エスケープ対象の単語がどれなのかなどについても情報がない。さてどうする。

RDBMS における主キーや参照整合性制約の外部キーは、ナチュラルキーよりもサロゲートキーを使う方がより変更に強くなる。

- ナチュラルキー

顧客コードなどの、ビジネスにおいて自然に発生するキー。自然キーともいう。

- サロゲートキー

レコードを一意に特定するためにシステムが振り出すキー。アイデンティファイア (Identifier) ともいう。Postgres だったらシリアル型、MS SQL Server だったら IDENTITY を使うのが簡単。

要するに、顧客コードとは別にシステムで振り出す ID を格納するカラムを作れということ。参照整合性制約を作成するときにも、サロゲートキーの方を使う。

- なぜサロゲートキーを使う方が良いのか

Web+DB Press の Vol.21 に詳しく書いてある。

顧客コードのフォーマットは変わるかもしれないから。変わってしまった場合、DB の再設計が必要だったり、データの整合性が失われたり、過去のデータとの比較ができなくなったりする。

- システム側で振り出したキーを使えと教えられたなあ

そういえば DB 設計をし始めた頃、「システム側で必ずキーを振り出せ」と尊敬する先輩から教えられたなあ。そのときはサロゲートキーという言葉ではなく、「システム側で振り出すキー」という表現だったけど。

それ以後、顧客コードなどがあってもシステム側で振り出すユニークな ID を格納するカラムを使うようになった。

- 参考

このメモは L'eclat des jours(2005-01-07) http://arton.no-ip.info/diary/20050107.html#p01 に触発されて書いた。

IDENTITY を設定して自動的に連番を振るようにしたカラムに、任意の値を INSERT したい。SET IDENTITY_INSERT を ON にすれば可能だ。

SET IDENTITY_INSERT
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_se ...

テーブルの ID 列に明示的な値を追加することを許可します。

セッション内の 1 つのテーブルのみが IDENTITY_INSERT プロパティを ON に設定できます。あるテーブルで既にこのプロパティが ON に設定されている状態で、別のテーブルに対して SET IDENTITY_INSERT ON ステートメントを実行すると、MicrosoftR SQL Server? は、SET IDENTITY_INSERT が既に ON であるというエラー メッセージを返し、このプロパティが ON に設定されているテーブルをレポートします。

挿入する値がテーブルの現在の ID 値を超える場合は、SQL Server は自動的に、新しく挿入された値を現在の ID 値として使います。

SET IDENTITY_INSERT は、解析時ではなく実行時に設定されます。

- SET IDENTITY_INSERT サンプル

DROP TABLE identity_test;
GO
CREATE TABLE identity_test (
  serialnum int IDENTITY (100, 1) PRIMARY KEY,
  num int
);

INSERT INTO identity_test (num) VALUES (101);
INSERT INTO identity_test (num) VALUES (102);

SET IDENTITY_INSERT identity_test ON;
INSERT INTO identity_test (serialnum, num) VALUES (128, 128);
SET IDENTITY_INSERT identity_test OFF;

INSERT INTO identity_test (num) VALUES (104);

SELECT * FROM identity_test;

上記クエリの実行結果。自動的に max() を取ってシーケンスの値を更新してくれるようだ。

serialnum  num
----------- -----------
100        101
101        102
128        128
129        104

時刻の扱いは データベースエンジンによって異なるので、クエリを作成する前に仕様や挙動を確認しておきたい。

たとえば、 time_column < '2004-08-27' のとき、'2004-08-27' は いつの時点を指すのか。2004-08-27 00:00:00 なのか、 2004-08-27 **:**:** 、つまりランダムなのか。いつもは時分秒まで指定していたので、省略して表記した場合はどうなるか調べておこう。

- SQL Server2000 の場合

日付時刻データの使用
http://www.microsoft.com/japan/msdn/library/ja/acdata/ac_8_c ...

時刻だけまたは日付だけを個別に格納するデータ型はありません。datetime 型または smalldatetime 型の値を設定する場合、時刻だけを指定すると、日付のデフォルト "January 1, 1900" が使用されます。日付だけを指定すると、時刻のデフォルト "12:00AM (深夜)" が使用されます。

要するに SQL Server2000 では、00:00:00 になるってことか。

- PostgreSQL 7.2.1 の場合。

ドキュメントは見つけられなかった。
日本PostgreSQLユーザー会: PostgreSQL 7.2.X 付属ドキュメント
http://www.postgresql.jp/document/pg721doc/

7.4 系列の文書も見てみたが、やはり明確な記述は見つけられなかった。
日本PostgreSQLユーザ会: PostgreSQL 7.4.X 付属ドキュメント
http://www.postgresql.jp/document/pg743doc/

まあ、この後やってみればわかるだろう。たぶん SQL Server2000 と同じ結果になると思うけど。

- CREATE TABLE

SQL Server2000 用。

CREATE TABLE time_where_test (utime datetime);

PostgreSQL 7.2.1 用。

CREATE TABLE time_where_test (utime timestamp);

- INSERT 時の展開

まずは INSERT のテスト。
結論から言うと、SQL Server2000 と PostgreSQL 7.2 ともに 'YYYY-MM-DD' は 'YYYY-MM-DD 00:00:00:000' に展開される。以下、サンプル。

PostgreSQL 7.2.1 でも SQL Server2000 でも同じ結果になった。以下、Postgres の出力結果。

hiroaki=> INSERT INTO time_where_test VALUES ('2004-06-18');
INSERT 93862442 1
hiroaki=> SELECT * FROM time_where_test;
        utime
---------------------
2004-06-18 00:00:00
(1 row)

- SELECT 時の展開

今度は SELECT 時のテスト。おそらく INSERT 時と同じように展開されるだろうけど、念のため。

さっきのテーブルを DROP して CREATE しなおし、8レコード分のテストデータを INSERT する。

INSERT INTO time_where_test VALUES ('2004-08-09 02:00:00');
INSERT INTO time_where_test VALUES ('2004-08-20 03:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 00:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 04:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 23:59:59');
INSERT INTO time_where_test VALUES ('2004-08-27 00:00:00');
INSERT INTO time_where_test VALUES ('2004-08-27 09:00:00');
INSERT INTO time_where_test VALUES ('2004-08-27 23:59:59');

INSERT が終わったら SELECT してみる。今度は SQL Server2000 での出力。

SELECT * FROM time_where_test WHERE utime < '2004-08-26' ORDER BY utime;

    utime
------------------------------------------------------
2004-08-09 02:00:00.000
2004-08-20 03:00:00.000

(2 件処理されました)

予想通り、2004-08-26 は 2004-08-26 00:00:00 に展開された。

イコールを付ければ8/26のレコードもヒット。

SELECT * FROM time_where_test WHERE utime <= '2004-08-26' ORDER BY utime;

utime
------------------------------------------------------
2004-08-09 02:00:00.000
2004-08-20 03:00:00.000
2004-08-26 00:00:00.000

(3 件処理されました)

Postgres でも同じように動作した。

トランザクションログを一時間間隔でバックアップするように設定したあと、イベントログに「情報」レベルのメッセージが記録されていた。気になったので D:\sqldata\MSSQL\LOG を確認したところ、以下の行が存在していた。

2004-08-03 16:47:35.41 spid54    エラー : 15457、レベル : 0、状態 : 1
2004-08-03 16:47:35.41 spid54    設定オプション 'show advanced options' が 1 から 1 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。。

Books Online を読む前に、Google で 15457 SQL Server を検索するとヒット。
MSrepl_commands
http://www.sql-server-performance.com/forum/topic.asp?ARCHIV ...

Error 15457 are informational messages and not really error messages, if you change server properties then you will see this message. When using SP_CONFIGURE statement the message will be issued to run RECONFIGURE, refer to BOL for more information.

要するに設定変更したよというメッセージでしかないわけだ。エラーじゃないなら無視するようにしよう。

SQL Server からダンプしたファイルを Enterprise Manager を使って別のマシンにリストアするときの手順。

バックアップを取得したサーバと復元するサーバが同一の場合は、リポジトリにバックアップの一覧が表示されるためもう少し簡単になる。しかし、本番サーバのバックアップを開発環境でリストアするときなどは以下の手順が必要。

SQL Server Books Online バックアップ デバイスからバックアップを復元する方法 (Enterprise Manager)

バックアップ デバイスからバックアップを復元するには

1. サーバー グループを展開し、サーバーを展開します。


2. [データベース] フォルダを展開し、データベースを右クリックして、[すべてのタスク] をポイントし、[データベースの復元] をクリックします。


3. [データベースとして復元] ボックスで、復元するデータベースの名前が既定の名前と異なる場合は、名前を選択するか入力します。データベースを新しい名前で復元する場合は、新しいデータベース名を入力します。

注  データベースに新しい名前を指定すると、データベース バックアップから復元されるデータベース ファイルの新しい名前が自動的に決まります。

4. [デバイスから] をクリックし、[デバイス選択] をクリックします。


5. [復元元] ボックスの [テープ] または [ディスク] をクリックし、復元元のデバイスをクリックします。
デバイスが表示されない場合は、[追加] をクリックして既存のバックアップ デバイスを追加するか、または新しいバックアップ デバイスを作成します。[データベースの復元] ダイアログ ボックスで、[内容表示] をクリックし、復元するバックアップ セットをクリックします。

以下略。

- リストア後の注意点

2003-09-09 の「データベースのリストアでユーザ情報が壊れる」で触れた不具合に注意。
「274188 - [SQL]PRB: 対応するログインの存在しないユーザー」
http://support.microsoft.com/default.aspx?scid=kb;JA;274188

対応するログインの存在しないユーザーに関する問題の解決手順

上記手順で確認した対応するログインの存在しないユーザーに関して、次のコマンドを実行します。
    Use Northwind
    go
    sp_change_users_login 'update_one', 'test', 'test'

これは、サーバー ログイン "test" を Northwind データベース ユーザー "test" に再度関連付けます。sp_change_users_login ストアド プロシージャを "auto_fix" パラメータとともに実行することで、対応するログインの存在しないユーザーすべてを更新することもできます。しかし、これは推奨されません。なぜなら、SQL Server は名前によってログインとユーザーを一致させようとするからです。ほとんどの場合には、これは期待したとおりの結果になります。しかし、誤ったログインがユーザーに関連付けられた場合、ユーザーは不正な権限を持ってしまいます。

文法は以下の通り。

sp_change_users_login 'update_one', '@UserNamePattern', '@LoginName'

以下のカラムを持つテーブルがある。

tel_shigai VARCHAR(6), -- 電話番号 市外局番
tel_shinai VARCHAR(6), -- 電話番号 市内局番
tel_bango VARCHAR(4), -- 電話番号

これを 出力するときは - で連結せよ、という仕様があった。

今までは単純に

tel_shigai + '-' + tel_shinai + '-' + tel_bango AS tel_no

としていたが、これだとどれかが NULL か 空文字でもハイフンがくっついてしまう。
全部空の場合は -- という出力になってしまう。

ということでユーザ定義関数を作成した。
郵便番号を連結する場合は、第三引数を null として渡す必要がある。
引数を可変長の配列で受け取ったりできればループを使って簡潔に書けそうだが、
どうも SQL Server2000 ではできないようなので if 文を連ねてみた。

-- 郵便番号、電話番号、FAX 番号を - で連結する
GO
DROP FUNCTION cat_number;
GO
CREATE FUNCTION cat_number(@arg1 VARCHAR(8), @arg2 VARCHAR(8), @arg3 VARCHAR(8))
RETURNS VARCHAR(64)
AS BEGIN

  DECLARE
    @str VARCHAR(64)
  IF (@arg1 IS NOT NULL) AND (@arg1 <> '') AND (@arg2 IS NOT NULL) AND (@arg2 <> '') BEGIN
    SET @str = @arg1 + '-' + @arg2
  END ELSE BEGIN
    SET @str = COALESCE(@arg1, '') +  COALESCE(@arg2, '')
  END

  IF (@str IS NOT NULL) AND (@str <> '') AND (@arg3 IS NOT NULL) AND (@arg3 <> '') BEGIN
    SET @str = @str + '-' + @arg3
  END ELSE BEGIN
    SET @str =  COALESCE(@str, '')  +  COALESCE(@arg3, '')
  END

  RETURN @str
END

CONCAT_NULL_YIELDS_NULL をオフにしておかないと、
NULL と文字列連結した結果は必ず NULL になってしまう。

SET CONCAT_NULL_YIELDS_NULL
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_se ...

MS SQL Server2000 に本番サーバからダンプしたデータをテストサーバにリストアしたところ、データ自体は正常にリストアされたようだが、どうもユーザ情報やログイン情報が変わってしまっているようだ。Google で SQL Server データベース リストア ユーザを検索すると MS の Knowledge Base がヒット。

データベースをリストアしたとき、ダンプ元とリストア先のユーザ情報・ログイン情報が異なる場合、リストア先でユーザ情報・ログイン情報が壊れるというバグだった。

回避方法はログイン情報とユーザ情報を作り直す、だそうだ。手間がかかるなあ。

KB274188 [SQL]PRB: 対応するログインの存在しないユーザー
http://support.microsoft.com/default.aspx?scid=kb;JA;274188

KB168001 [SQL]PRB: リストア後のユーザーログイン、権限エラー
http://support.microsoft.com/default.aspx?scid=kb;JA;168001

ディスク障害などでリストアするときも同じ問題が発生しそうだな。

トランザクションログがいっぱいになると、その時点で DB が停止する。
今回のプロジェクトではどのような運用が望ましいのかを調査。

- フルバックアップで十分。

今回のプロジェクトで使用している SQL Server では、毎日 2:30 に DB のフルバックアップを取得している。この時点で前回バックアップからのトランザクションログを破棄してしまって構わない。また、トランザクションログのサイズの見積もりは、以下のようにして行う。

[SQL:02744] Re: トランザクションログの見積り
http://www.users.gr.jp/ml/archive/sql/2744.asp

現在のトランザクションログサイズは、814MBだ。これだけあれば十分だろう。

SQL Server2000 では、NULL 同士でもユニーク制約 (一意性制約)にひっかかる。SQL Server2000 にこんな落とし穴があるなんて思わなかった。危ないところだった。

PostgreSQL 7.3.2 ユーザガイド Chapter 2. データ定義 2.4.3. 一意性制約
http://www.postgresql.jp/document/pg732doc/user/ddl-constrai ...

一般に、制約の対象となる列について、同じ値をもつ行が 2 行以上ある場合は、
一意性制約違反になります。
しかし、ここでは NULL 値は等価とは見なされません。
つまり、複数列での一意性制約では、制約対象の列の少なくとも 1 つに
NULL 値をもつ行を無限に格納することができるということです。
この振る舞いは SQL 標準に準拠していますが、この規則に従わない
SQL データベースもあるそうです。
ですから、移植する予定のアプリケーションを開発する際には注意してください。

- 検証 SQL

DROP TABLE uniq_test;
CREATE TABLE uniq_test(
  int1 int,
  int2 int,
  UNIQUE (int1, int2)
);
INSERT INTO uniq_test (int1, int2) VALUES (NULL, 1);
INSERT INTO uniq_test (int1, int2) VALUES (NULL, 1);

- PostgreSQL 7.2.1 での結果

hiroaki=> DROP TABLE uniq_test;
DROP
hiroaki=> CREATE TABLE uniq_test(
hiroaki(>  int1 int,
hiroaki(>  int2 int,
hiroaki(>  UNIQUE (int1, int2)
hiroaki(> );
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'uniq_test_int1_key' for table 'uniq_test'
CREATE
hiroaki=> INSERT INTO uniq_test (int1, int2) VALUES (NULL, 1);
INSERT 31376096 1
hiroaki=> INSERT INTO uniq_test (int1, int2) VALUES (NULL, 1);
INSERT 31376097 1

- SQL Server2000 での結果

(1 件処理されました)

サーバー : メッセージ 2627、レベル 14、状態 2、行 1
UNIQUE KEY 違反、制約 'UQ__uniq_test__65851878': オブジェクト 'uniq_test' には重複したキーは挿入できません。
ステートメントは終了されました。

NULL は NULL なんだから重複って概念はおかしい。

COALESCE() は、引数のうち NULL でないものを返す。CASE や NULLIF を使っても同じことができるけど、こっちの方が簡潔。可変長の複数の引数を指定できて便利。

- COALESCE リファレンス

PostgreSQL 7.3.4 ユーザガイド Chapter 6. 関数と演算子 条件式
http://www.postgresql.jp/document/pg734doc/user/functions-co ...

6.12.2. COALESCE
COALESCE(value [, ...])
COALESCE 関数は、NULL でない自身の最初の引数を返します。 データを表示の目的で取り出す際、NULL 値の代わりにデフォルト値を使う場合、時として便利なことがあります。 その例です。

SELECT COALESCE(description, short_description, '(none)') ...

COALESCE
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_ca ...

- COALESCE のスペルが覚えられない

ところで、私は COALESCE のスペルがなぜか覚えられないため、この記事を書いた。COLESE とか COALESE とか COLESCE などと書いてしまい、文法エラーになることが多かった。最近では MS SQL Server 付属のクエリアナライザで SQL を書くようになり、キーワード色づけの恩恵を受けるようになったため間違いが減ったけど。それでも時々忘れることがあって、そのときはクエリアナライザの顔色を伺いながらタイプしている。キーワードに色が付いたら正解スペル、色が付かなかったら間違いスペルだ。まさに顔色を伺ってる感じだ。

COALESCE の発音は「こぅあれす」なんだけど、「す」のところが SCE になっていることが覚えられない原因だと思う。最初は「こぉりーす」と誤読していたこともスペルミスの原因かも。

coalesce goo 英和辞典
http://dictionary.goo.ne.jp/search.php?MT=coalesce&kind= ...

bcp で NULL をどう扱うかはオプションで指定可能。

http://www.microsoft.com/japan/msdn/library/ja/coprompt/cp_b ...

-k 一括コピー操作時、空の列には、挿入される列の既定値ではなく、NULL 値を保持すること指定します。

てにをはが抜けてるけど、気にするな。

SQL Server で使う Transact-SQL における文字列のエスケープ。

Transact-SQL リファレンス Constants (定数)
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_ca ...

単一引用符で囲まれた文字列に単一引用符を埋め込む場合は、
単一引用符を 2 つ続けて並べることで 1 つの単一引用符を表します。
文字列が二重引用符で囲まれている場合は該当しません。

\' などには対応していない模様。
設定によっては、文字列をダブルクオートで括ることができるようだ。

- sa アカウント

SQL Server の管理者用アカウント。
初期設定ではパスワードが空のままになっているため、ワームに狙われることが多い。
必ず強固なパスワードを設定しておくこと。

- 権限

以下の三つがある。
SQL Server への接続権。
データベースへの接続権。
テーブルなどのオブジェクトへのアクセス権。

- ロール

権限をパッケージ化したもの。
「固定サーバロール」、「固定データベースロール」などがある。
db_backupoperator に所属させておくと、その DB をバックアップできる権限が与えられる。
他にいろいろ設定する必要が無くてお手軽なようだ。
もっとも、業務で使うなら「本当に余計な権限が付与されない」ことを確認する必要がある。
「DROP TABLE はできないと思ってたんだけど、試したらできちゃった」なんてことが無いように。

- 事前準備

Microsoft Search サービスを起動しておく。
DB のフルテキストインデックスを有効にする。
テーブルのフルテキストインデックスを有効にする。
列のフルテキストインデックスを有効にする。
フルテキストカタログを有効にする。

追記。
ちなみに、サーバが MS SQL Server 2000 の場合、「テーブルのフルテキストインデックスを有効にする」などの作業は DB がインストールされているサーバ上でおこなう必要がある。リモートから Enterprise Manager を起動して接続している場合は該当項目がグレーアウトして GUI から選択できない。サーバが MS SQL Server2005 の場合は、リモートから設定可能。

検索対象とするテーブルには、UNIQ で NOT NULL なカラムが必要。

- クエリの例

SELECT syogo FROM company_search WHERE CONTAINS (*, '工業');

- ノイズ語 (インデックスされない単語) を納めたファイルは \mssql\ftdata\config にある。

- FREETEXT は OR 検索しかできない。CONTAINS は AND 検索が可能。

もっとも、FREETEXT でも複数の FREETEXT を AND で連結することで AND 検索はできる。

- SELECT をブロックするには XLOCK じゃないとダメなの?

MS SQL Server2000 で SELECT をブロックするには UPDLOCK でいいと思ったが、XLOCK にしなければならないのだろうか? 私は UPDLOCK で 後発のトランザクションの SELECT がブロックされるのを確認済みなのだが・・・。もしかして、>>557 は XLOCK どころか UPDLOCK も試してなかったのだろうか?

SQL教えてっ！！ lt;3gt;
http://pc2.2ch.net/test/read.cgi/tech/1042208762/557-560

557 ：デフォルトの名無しさん ：03/05/06 17:35
SQLServer2000です。
SELECTと同時にロックをかける・・・Oracleでいう、FOR UPDATEみたいな
動作をさせたい場合はどうすれば良いのでしょうか？
ヘルプには、SELECT文に使うヒント句でロック方法を指定するような記述が
あるのですが・・・

どのヒントを指定しても別の接続からSELECTできてしまいます。
(UPDATE等は出来ないので、ロックはされているようです)


558 ：デフォルトの名無しさん ：03/05/06 17:52
>>557
FROM table_name (UPDLOCK) かな?


559 ：デフォルトの名無しさん ：03/05/06 18:00
すまん。UPDLOCK中でもSELECTはできるから排他ロックが必要なのかな。
FROM table_name (XLOCK)

この辺見るといいかも
http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/acdata/ac_8_con_7a_7xde.asp
http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/acdata/ac_8_con_7a_8um1.asp



560 ：557 ：03/05/06 18:05
>５５９
XLOCKで逝けました!!

>557で「どのヒントを指定しても」って書いたのにXLOCKを試していませんでした。
何故なら、印刷したヘルプのXLOCK以下が切れていた為(鬱死
今度はもっとヘルプをよく読みます。
ありがとうございました。

- 判明

わかった。結論から言うと、UPDLOCK では SELECT をブロックできない。ただし、後発のトランザクションの SELECT に UPDLOCK が指定されている場合は、ブロックされる。ロックの流れは以下の通り。

先発のトランザクションは、UPDLOCK 付きの SELECT によって 更新ロック (U) を獲得する。後発のトランザクションが UPDLOCK 付きの SELECT で更新ロック (U) を獲得しようとするが、既に 先発のトランザクションが更新ロック (U) を獲得済みのため、ロック解除待ちになる。

私がやったテストは、どちらのトランザクションも UPDLOCK 付きの SELECT だったため、後発のトランザクションの SELECT がブロックされたというわけだ。

つまり、UPDLOCK 無しの SELECT が発行される可能性があり、その SELECT に UPDLOCK 中のレコードを読ませたくない、という場合は XLOCK を使わなければならない。

参考: ロックの互換性 MSDN Library Japan
http://www.microsoft.com/japan/msdn/library/default.asp?url= ...

DB に格納されている文字列に対して、全文検索をかける。
いくつか方法があるようだ。
1. LIKE を 使って地道に SQL を組み立てる
2. CONTAINS を使う
3. FREETEXT を使う。
FREETEXT というのは知らなかった。検索語の分解もやってくれるようだ。結構便利かも。

FREETEXT
http://www.microsoft.com/japan/developer/library/tsqlref/ts_ ...

魅惑（困惑）のSQL Server
http://www.unisys.co.jp/club/net_view/20030129.html

このデッド・ロックの原因を理解するにあたって必要な知識は「SQL Serverは他のユーザがcommit/rollbackするまで当該行の読み取りをブロックする」ということだけでした（ORACLEの場合はトランザクション開始前のデータを読み取る）．SQL Serverはロックをエスカレーションさせるとか，分離レベルを上げた場合の振る舞いとか，そういう難しい話は全く関係なし．

あとで試してみよう。

TCP 1433 と UDP 1434。
このポート番号を検索してみたところ、ほとんどがワームについての対策サイトだった。
TCP 1434 と UDP 1433 も使用するという記述を見かけたが、netstat -an してもそのポートで listen している様子はなかった。ネットワーク管理者には使用するポートを届け出なければならないので、引き続き調査が必要。

以下のページに書かれていた。
[INF] ファイアウォール経由での SQL Server との通信に必要な TCP ポート
http://support.microsoft.com/default.aspx?scid=kb;JA;287932

SQL Server は、ソケット ネットワーク ライブラリを使用して TCP/IP 上で通信する Winsock アプリケーションです。SQL Server は特定のポートで接続要求を待ち受けます。SQL Server 用のデフォルトのポートは 1433 です。SQL Server 用のポートは 1433 である必要はありませんが、1433 は SQL Server の公式の IANA (Internet Assigned Number Authority) ソケット番号です。

UDP 1434 はサーバ名などの情報を送るために使っている模様。

さらに、SQL Server 2000 の名前付きインスタンスは、デフォルトでは、動的な送信先ポートを使用します。このポートは、ファイアウォールを構成する前に、固定ポートに変更する必要があります。送信先ポートを構成するには、SQL Server ネットワーク ユーティリティを使用します。SQL Server ネットワーク ユーティリティの使用方法の詳細については、SQL Server Books Online を参照してください。

さもなければ、クライアント コンピュータはランダムな UDP ポートを開く必要があります。そして、サーバーは UDP ポート 1434 を使用して、インスタンス名を送信します。インスタンスがクラスタ化されている場合、SQL インスタンスのバージョン、インスタンスが受信待ちしている TCP ポート番号、およびインスタンスが使用している名前付きパイプを送信します。ただし、ファイアウォールで開くポート番号を最小限にすることが目的の場合は、デフォルトのインスタンスおよびすべての名前付きインスタンスで使用する静的なポート番号を選択する必要があります。クライアント コンピュータは、特定の ServerName または ServerName インスタンスおよび固定のポート番号に接続するように構成する必要があります。

2003-06-03 のトリガは、もっと簡潔に書けることに気づいた。環境は MS SQL Server 2000。

- 冗長版

GO
DROP TRIGGER log_transaction_ao;
GO
CREATE TRIGGER log_transaction_ao ON users FOR INSERT, UPDATE AS
BEGIN
  INSERT INTO users_log SELECT * FROM inserted
  IF (SELECT COUNT(*) FROM inserted WHERE commit_master = 1) > 0 BEGIN
    DECLARE @user_cd VARCHAR(5)
    SELECT @user_cd = user_cd FROM deleted
    IF (@user_cd IS NOT NULL)  BEGIN
      DELETE FROM user_master WHERE user_cd = @user_cd
    END
    INSERT INTO user_master SELECT * FROM inserted
  END
END

- 簡潔版

GO
DROP TRIGGER log_transaction_users;
GO
CREATE TRIGGER log_transaction_users ON ao FOR INSERT, UPDATE AS
BEGIN
  INSERT INTO user_log SELECT * FROM inserted
  IF (SELECT COUNT(*) FROM inserted WHERE commit_master = 1) > 0 BEGIN
    DELETE FROM user_master WHERE user_cd IN (SELECT user_cd FROM deleted)
    INSERT INTO user_master SELECT * FROM inserted
  END
END

- トリガを使うことで、INSERT/UPDATE 文を複数のテーブルにそのまま反映させることができる。

- 「ユーザが入力した内容の履歴を取りたい」というリクエストを受けた。本番のテーブルへ投げられたクエリと同じクエリを別途用意したログ用のテーブルに追記していく、というやり方で実現できるだろう。

また、マネージャからは、「プログラム側で複数のテーブルに SQL を投げるのはやらせたくない。欠落したりするおそれがある。何か仕組みを作れないか考えてみてくれ。」とも言われている。

- 解決策

ワーク用テーブルとマスターテーブルとログ用テーブルを用意する。
ログ用テーブルのテーブル定義は、マスターテーブルと基本的に同じだが、
ユニーク制約、参照整合性制約などは指定しないようにする。
本番用テーブルに以下のような INSERT/UPDATE トリガをセットする。
OLD 特殊テーブルを参照し、その内容をログ用テーブルに INSERT する。

MS SQL Server では、OLD テーブルは inserted と呼ばれている。
postgres などとは名前が違うだけで、機能は変わらないようだ。
ただ、Postgres はトリガの発動タイミングを指定できるが、
MS SQL Server 2000 では指定できない。トリガが発動するのは、制約チェックなどの後だけだ。
・・・と思ったら、MS SQL Server 2000 にも事前に発動するトリガがあるそうな。
INSTEAD OF トリガというもので、従来の事後発動トリガは AFTER トリガと呼ぶとのこと。

- サンプル

-- log テーブルへは無条件に INSERT する。
-- master テーブルへは、commit_master カラムが1の時のみ DELETE & INSERT する。
INSERT INTO user_master SELECT * FROM inserted では、inserted が複数レコードでもすべて INSERT してくれる。

DROP TRIGGER log_transaction_user;
CREATE TRIGGER log_transaction_user ON user FOR INSERT, UPDATE AS
BEGIN
  INSERT INTO user_log SELECT * FROM inserted
  IF (SELECT COUNT(*) FROM inserted WHERE commit_master = 1) > 0 BEGIN
    DECLARE @user_cd VARCHAR(5)
    SELECT @user_cd = user_cd FROM inserted
    DELETE FROM user_master WHERE user_cd = @user_cd
    INSERT INTO user_master SELECT * FROM inserted
  END
END

以下のエラーメッセージが出てしまう。

リレーションシップ 'FK_customer_annual_prefecture' を作成できません。
ODBC エラー : [Microsoft][ODBC SQL Server Driver][SQL Server]テーブル 'customer_annual' に
設定しようとしている
FOREIGN KEY 制約 'FK_customer_annual_prefecture' はパスが循環するか、
複数に連鎖する可能性があります。
ON DELETE NO ACTION、ON UPDATE NO ACTION、を指定するか、
ほかの FOREIGN KEY 制約を変更してください。
[Microsoft][ODBC SQL Server Driver][SQL Server]制約を作成できませんでした。
以前のエラーを調べてください。

よく考えたら UPDATE CASCADE 使う必要性が薄いので、指定を外して解決。

自動的に DB 側でユニークな番号を振り出してくれる機能を使いたい。この機能は Postgres では シーケンス型と呼ばれていたが、MS SQL Server ではオートナンバー型と呼ぶとのこと。

- オートナンバー型 を使った CREATE TABLE サンプル

CREATE TABLE sequence_test (
  sequence_id int IDENTITY (1, 1) PRIMARY KEY,
  insert_time datetime
);

該当のカラムには NOT NULL を指定する必要がある。PRIMARY KEY でもいいけど。

あとの INSERT などの流儀は Postgres と同じ。

INSERT INTO sequence_test (insert_time) VALUES (CURRENT_TIMESTAMP);

ロールバックしても番号が進むので、連番が保証されるわけではないのは Postgres と同じ。というか、連番が保証される仕組みを持ったシーケンス型ってあるのかな。異常にコストが高そうだ。