For example, I'm going to build a 1,000,000-line digital table:
CREATE TABLE dbo. Nums (n INT not NULL PRIMARY KEY);
DECLARE @max as int, @rc as int;
SET @max = 1000000;
SET @rc = 1;
INSERT into Nums VALUES (1);
While @rc * 2 <= @max
BEGIN
INSERT into dbo. Nums SELECT n + @rc from dbo. Nums;
SET @rc = @rc * 2;
End
INSERT into dbo. Nums
SELECT n + @rc from dbo. Nums WHERE n + @rc <= @max;
This way is very ingenious, it is not a one of the loop inserts, but inserts many rows at a time, {1},{2},{3,4},{5,6,7,8} ...
Why is it so fast?
is because it saves the time to compare and record these logs compared to other available solutions.
The author then gives a recursive solution to a CTE:
DECLARE @n as BIGINT;
SET @n = 1000000;
With Nums as
(
SELECT 1 as N
UNION All
SELECT n + 1 from nums WHERE N < @n
)
SELECT N from Nums
OPTION (maxrecursion 0);--To remove the recursive limit of the default 100
A better solution for a CTE is to have a lot of lines, and then compute with row_number, then select Row_number the value of the column.
Copy Code code as follows:
DECLARE @n as BIGINT;
SET @n = 1000000;
With Base as
(
SELECT 1 as N
UNION All
SELECT n + 1 from Base WHERE N < CEILING (SQRT (@n))
),
Expand as
(
SELECT 1 as C
From base as B1, base as B2
),
Nums as
(
SELECT row_number () over (order by C) as N
From Expand
)
SELECT N from nums WHERE n <= @n
OPTION (maxrecursion 0);
With the continuous accumulation of Cartesian product, the 22n line is reached.
Finally, the author gives a function to generate such a numeric table:
Copy Code code as follows:
CREATE FUNCTION dbo.fn_nums (@n as BIGINT) RETURNS TABLE
as
return
with
L0 as (select 1 as C-UNION all SELECT 1),
L1 as (select 1 as C to L0 as A, L0 as B),
L2 as (SEL ECT 1 as C from L1 as a, L1 as B),
L3 as (SELECT 1 as C to L2 as a, L2 as B),
L4 as (select 1 as C from L3 as A, L3 as B),
L5 as (select 1 as C from L4 as A, L4 as B),
Nums as (select Row_number () over (order by C) as N FRO M L5)
SELECT n from nums WHERE n <= @n;
Go
