「IDENTITY属性を持つ列を探すには」をお題にやったことをBlogる

昨日、任意のテーブルからIDENTITY属性を持つカラムを得るにはどうしたらいいのか、という質問をPASSJ(SQL Serverユーザグループ)のDevMLに投げました。
そこでのやり取りや、私のとった行動がそのまま記事になるかな…と思い、ここに残して起きます。

Step1 後輩から相談を受ける
「Javaを利用して任意のテーブルのX件目からY件目までを取得するメソッドを作成したい」
という後輩のリクエストがありました。
Oracle対応のSQLは既に別の人が作っていたので、SQL Server対応をしたいとのこと。

SQL ServerではOracleでいうところのROWNUM擬似列はないので、別の機能を利用して作成する必要があります。(Yukonでは実装される予定とのこと)
そこで、こういう場合のセオリーとしてはidentity関数もしくはidentity列を利用して実現するというのがあります。
ここで注意しなければならないのは、identity関数を利用するには、select 文中に into 句を利用するという制限がつきます。
ですのでidentityを利用するには、どうしても1クエリでは実現不可能なのです。

幸い(？)SQL Serverでは下手にUnionを使うよりかは一時テーブルを使うほうがはるかにパフォーマンスがいいということがだいぶと利用者の中で広まりつつあります。
しかし、一時テーブルを使うためにはSQL ServerのtempdbというDBについての知識が必要ですが、そこはきちんとBooks Onlineで抑えておいてくださいね。

話を元に戻します。
pubsDBのemployeeテーブルでfname順に3件目から8件目を得るには、こんなクエリを書くと一番効率がいいと思われます。

 use pubs
 go

 select
 　identity(int, 1, 1)	rank,
 　j.*
 into #testtbl
 from employee j
 order by j.fname

 select *
 from #testtbl
 where rank between 3 and 8

ところが、identityには制限があり、1テーブルにつき1列しか設定することができません。
ですので、pubsDBのjobsテーブルのmin_lvlで昇順に並べた場合、3件目から8件目までを取得するとなると、こんな工夫が必要になります。

 use pubs
 go

 select
 　identity(int, 1, 1)	rank,
 　cast(j.job_id as int) job_id, j.job_desc, j.min_lvl, j.max_lvl
 into #testtbl
 from jobs j
 order by j.min_lvl

 select *
 from #testtbl
 where rank between 3 and 8

検索対象テーブルにidentity列を利用しているカラムがある場合には、そいつを無効化してやらないといけません。
そのため、上記の例ではcastをすることでidentity属性を殺したというわけです。

ここで、

任意のテーブルに対してX件目からY件目を取得するためには
任意のテーブルにIDENTITY列があるのかどうかの情報が必要

になるということがわかりました。

そこで、Books Onlineを調べてみると、ident_currentという関数があることがわかりました。

例1) identity属性あり

 select ident_current('jobs')

結果は「14」

例2) identity属性なし

 select ident_current('employee')

結果は「Null」

数値が返って来ると取りあえずIDENTITY列は必ずあると判断できるようです。
ただし、Nullが返って来るからと言ってIDENTITY列がないとは限らない可能性があるようです。[根拠]
上記[根拠]の対処方法はチトわかりませんが、とりあえずIDENTITY列がある場合にはそれなりの対処はしてやろうということで、今度は次の課題にかかります。

どのカラムがIDENTITYなのかを調べる

とりあえず、システムテーブルに情報があるかもと思い、syscolumnsの仕様を確認してみることにしました。
…ざっと眺めてみましたが、とりあえずなさそうです。
他のシステムテーブルにidentity情報を格納しているのかも…と思い、他のシステムテーブルも見てみましたが、その形跡はありません。
同様に、Microsoft社のサイトでも検索をかけてみましたが、欲しい情報は得られませんでした。

Step2 情報提供を呼びかける
Books Onlineをあさってみただけの状況ですが、とりあえずここで情報提供を呼びかけようとし、先に書いたPASSJのDevMLに、上記に書いた状況をまとめて投げてみました。[詳細]

とはいうものの、そのまま情報提供を待っているだけではただの「教えてクン」なので、思いつく限りのことをしなければ…

思いついたのはEnterpriseManagerを起動して、IDENTITY情報が得られる画面があったかどうかを確認することでした。
案の定、テーブルのプロパティからカラムの情報を見ると、きっちりとIDENTITY情報があったので、これを取得できれば問題は解決すると思われます。

ここで、プロファイラの登場です。

プロファイラはSQL Serverに流れるトラフィックをトレースするためのソフトウェアで、必要な部分を切り取ってリスト化するというモノです。
利用用途としては、

プログラムが正しいクエリを作成しているかを確認
パフォーマンスが悪いクエリの検索
デッドロックの原因の特定
ストアドプロシージャのパフォーマンスの監視

などといったところが上げられます。
が、CAMUSは便利なウラコマンド(BooksOnlineに載っていないプロシージャなど)を探すために使っているということが多いです。(^^;)
コイツはSQL Serverをインストールしたのであれば、大概勝手についてくるはずです。ご確認を。

こいつのトレース結果からIDENTITY情報の得方がわかるはず。
案の定、判りました。(^^;)

 sp_MShelpcolumns N'dbo.jobs', null, 'id', 1

コイツを実行すると、第1引数に指定したテーブルの絡む情報を得ることができます。
そのうち、col_identity が 1ならば、identity、0ならばidentityでないということが判りました。

こいつを使えばできそうです。
というわけで、先に投げたDevMLの返信で、上記の調査結果を報告しました。[詳細]

Step3 情報がよせられる
松本さまより、たにぐちさまのBlogに情報があるとの情報をいただきました。[こちら]
さらにsp_helpをみたら、IDENTITY情報があったよとの情報もおがわみつぎさまからいただきました。

どうやら、IDENTITY列であるかどうかを判断するには、4パターンあるようです。

syscolumnsテーブルcolstat列(smallint型)の1ビット目で判断
sp_MShelpcolumns実行で得られる結果セットより、col_identityの値で判断
syscolumnsテーブルstatus列の値で判断
sp_helpを使う

ここで「？」と思ったのが、最初と3番目の方法です。
最初の方法は仕方ないのです。BooksOnlineでも「内部使用のみ。」という記述がされていたので、公開されていない方法だと考え、触れませんでした。
アレ？と思ったのは3番目の方法。
BooksOnlineではこう書かれています。

列またはパラメータのプロパティを説明するビットマップ。
0x08 = NULL 入力を許可する列。
0x10 = varchar 型または varbinary 型の列が追加されたとき、ANSI 埋め込みが効果的です。varchar 型の列の場合、後続する空白が保存され、varbinary 型の列の場合、後続する 0 が保存されます。
0x40 = OUTPUT パラメータ
0x80 = ID 列

松本さん曰く、0x80だとidentityだとのことですので、BooksOnlineの記述を鵜呑みにしてしまった私には、まさかこれが…とは思いませんでした。

Step4 別のお役立ち情報も寄せられる
BooksOnlineの記述(日本語訳)に誤訳があるとのことで、河端さまがMSにフィードバックしてくださいました。[フィードバック内容]
おまけにフィードバックの仕方と、最新版のBooksOnlineだと、BooksOnlineからダイレクトにフィードバックができるよということも教えていただきました。[その詳細]