Maybe you remember my old post
NAV integer table in SQL-statement.
It does work but has some drawbacks: it works well for small tables but get very fast very slow with a greater number of rows.
There are a lot of possible methods that are better. The best (most of the time) is the Itzik-Style Cross-Join.
WITH E1(N) 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 ), -- 1*10^1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1
, -- 1*10^2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2
, -- 1*10^4 or 10,000 rows E8(N) AS (SELECT 1 FROM E4 a, E4
-- 1*10^8 or 100,000,000 rows SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8;For some other possible ways, I’ll point to the article I read about it.
Hidden RBAR: Counting with Recursive CTE’s
Читать дальше