SQL で動的に連番テーブルを生成する

SQL で動的に連番テーブルを生成するには？

以下のように、単純に 1 から順番に、ある特定の数までの連番を持つテーブルを動的に生成したい時ありませんか？

動的に連番を生成する方法はいろいろありますが、今回は 1 から 100 までの連番を簡単に生成する 2 つの方法をご紹介します。

sys.all_objects を利用して連番を生成する方法

まずは sys.all_objects を利用して、連番を生成する方法です。

DECLARE @Count INT = 100;

SELECT   TOP (@Count)
         ROW_NUMBER() OVER (ORDER BY object_id) AS SeqNo
FROM     sys.all_objects
ORDER BY SeqNo;

確実にレコードが存在するであろうテーブルを利用して、キー順に ROW_NUMBER を振って、連番テーブルを生成しています。

sys.all_objects はシステムオブジェクトやユーザーが定義したオブジェクト等を保持している、オブジェクトカタログビューで、SQL Server のコアなビューなので、バージョンがあがっても簡単になくなったりする心配はないと思います。

ですが、sys.all_objects のデフォルトのレコード数は、SQL Server のバージョンによっても違いますが、だいたい 2000 前後なので、あまり大きいを生成するのには使えませんので気をつけてください。

共通テーブル式：CTE (Common Table Expression) を使って連番を生成する方法

次は共通テーブル式 (CTE) を使用する再起クエリー使って連番を生成する方法です。

DECLARE @Count   INT = 100;

;WITH NumberTable (SeqNo)
AS
(
     SELECT 1
     UNION ALL
     SELECT 1 + SeqNo
     FROM   NumberTable
     WHERE  SeqNo < @Count
)
SELECT  SeqNo
FROM    NumberTable
OPTION (MAXRECURSION 0);

以前ご紹介した「日付範囲から日ごとのデータを作成する」クエリーとほぼ同じです。

クエリーヒントの MAXRECURSION を使って、再起を繰り返す回数を指定することができ、ここでは 0 （無制限）を指定しています。

この方法は、数に上限はありませんが、@Count があまり大きな数になると、パフォーマンスが悪くなりますのでご注意くださいね！

最後に、連番テーブルの使用頻度が高かったり、大きな数の連番が必要だったりする際には、動的に作らず、連番用のテーブルを作っておいて利用するほうが良いと思います。