SQLにて連番を生成するという需要は時々あり、私の場合はテストデータやダミーデータの生成に用いることが多いです。
再帰CTEを用いたもの
検索すると良く出てくるのが、下記のような再帰CTEを用いたものです。
-- original: https://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/ DECLARE @start INT, @end INT SET @start = 1 SET @end = 1000 ;WITH CTE(Number) as ( SELECT @start UNION ALL SELECT [Number] + 1 FROM CTE WHERE [Number] < @end ) SELECT [Number] FROM [CTE] OPTION (MAXRECURSION 1000)
非常に分かりやすい上に高速に動作するのですが、OPTION( MAXRECURTSION )
で指定する再帰の回数は上限32,767
までとなっています。(デフォルトは100
)
docs.microsoft.com
32767個より多い数の連番を求める場合は、下記のように0
を与えれば再帰回数上限なしで動作します。
... SELECT [Number] FROM [CTE] OPTION (MAXRECURSION 0)
CROSS JOINを用いるもの
それで、なぜこんな話を持ち出したかというと、下記のように再帰CTEを用いずにCROSS JOIN
を用いるものを見かけたからです。
DECLARE @start INT, @end INT SET @start = 1 SET @end = 1000 -- original:http://tsql.solidq.com/SourceCodes/GetNums.txt ;WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2 L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 2^2 -> 4 L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 4^2 -> 16 L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 16^2 -> 4096 L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 4096^2 -> 65536 L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 65536^2 -> 4294967296 Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@end - @start + 1) @start + rownum - 1 AS n FROM Nums ORDER BY rownum;
CTEとCROSS JOIN
を活用して、約42億までの連番を生成することができます。
非常に美しいのですが、素朴な疑問が沸き起こります。「これ、速いの?遅くない?」と。
手元の環境で試したところ、上記の再帰CTEのバージョンより100倍近く遅くなりました。
速くしようとして下記のようにしてみましたが、それでも再帰CTEより50倍近く遅いままでした。
;With L0 (Number) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) , L1(Number) AS (SELECT 1 FROM L0 AS A CROSS JOIN L0 AS B) -- 16^2 -> 4096 , L2(Number) AS (SELECT 1 FROM L1 AS A CROSS JOIN L1 AS B) -- 4096^2 -> 65536 , L3(Number) AS (SELECT 1 FROM L2 AS A CROSS JOIN L2 AS B) -- 65536^2 -> 4294967296 , Nums(rownum) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM L3) Select TOP(@end - @start + 1) @start + rownum - 1 AS n From Nums