kriki: NAV integer table in SQL-statement - improved

[Blog bot]

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

Читать дальше