SQL Serverで検索結果に通し番号を打つ

ちょっと調べることがあったので。SQL Serverで、Select文による検索の結果にあらかじめ通し番号を打っておきたかったので調べてみました（2018年11月27日追記：相関サブクエリーと型付きデータセットでの自動通番機能による方法）。

以下、TestTableというテーブルにTestFieldという列があり、その列の内容順で通し番号を打つ場合について考えてみます。

ROW_NUMBERを利用する方法

SQL Serverが持つROW_NUMBER関数とWindow関数と呼ばれるOVERを使って、その場で並べ替えやパーティショニングを行ってその中でのポジションを取得する方法です。

SELECT ROW_NUMBER() OVER (
		ORDER BY TestField ASC
		) AS rowIndex,
	*
FROM TestTable

こんな感じだそうです。ROW_NUMBERとOVERはセットで指定する必要があります。ただし、この方法をVisual Studioなどの型付きデータセット用デザイナーで使おうとするとOverなどに対応していないというエラーが出ます（エラー後、私の環境ではDataSetデザイナーに変更が反映されないなどいろいろ問題がでました）。デザイナーを使うDataSetを扱う場合は別の方法を採った方が良さそうです。

ビッグデータ分析・活用のためのSQLレシピ

相関サブクエリーを利用する方法

同様の処理は下記のようなSQLでも実施できます。

SELECT (
		SELECT count(TestField)
		FROM TestTable inside
		WHERE inside.TestField <= outside.TestField
		) AS rowIndex,
	*
FROM TestTable outside

ただし相関サブクエリーは大量のデータに対して利用すると遅くなる可能性が高いので要注意です。小規模のデータなら問題無いかと。

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

型付きDataSetのプロパティで通番を設定する方法

型付きDataSetを用いて値を取得する場合、デザイン画面上で行を追加してから、列のプロパティにある「AutoIncrement」をTrueに設定、同じくAutoIncrementSeedとStepを設定すれば自動的に採番される行が追加されます。

参考: ROW_NUMBER関数

ROW_NUMBER (Transact-SQL)/SQL Server 2008

http://msdn.microsoft.com/ja-jp/library/ms186734(v=sql.100).aspx