SQL, Auxiliary table of numbers

vzczc_224 0
Viewed 23482

For certain types of sql queries, an auxiliary table of numbers can be very useful. It may be created as a table with as many rows as you need for a particular task or as a user defined function that returns the number of rows required in each query.

What is the optimal way to create such a function?

sql-server sql
8 Answers

The most optimal function would be to use a table instead of a function. Using a function causes extra CPU load to create the values for the data being returned, especially if the values being returned cover a very large range.

mrdenny_41970

From SQL Server 2022 you will be able to do

SELECT Value
FROM GENERATE_SERIES(START = 1, STOP = 100, STEP=1)

In the public preview of SQL Server 2022 (CTP2.0) there are some very promising elements and other less so. Hopefully the negative aspects can be addressed before the actual release.

✅ Execution time for number generation The below generates 10,000,000 numbers in 700 ms in my test VM (the assigning to a variable removes any overhead from sending results to the client)

DECLARE @Value INT 

SELECT @Value =[value]
FROM GENERATE_SERIES(START=1, STOP=10000000)

✅ Cardinality estimates

It is simple to calculate how many numbers will be returned from the operator and SQL Server takes advantage of this as shown below.

enter image description here

❌ Unnecessary Halloween Protection

The plan for the below insert has a completely unnecessary spool - presumably as SQL Server does not currently have logic to determine the source of the rows is not potentially the destination.

CREATE TABLE dbo.NumberHeap(Number INT);

INSERT INTO dbo.Numbers
SELECT [value]
FROM GENERATE_SERIES(START=1, STOP=10);

When inserting into a table with a clustered index on Number the spool may be replaced by a sort instead (that also provides the phase separation)

enter image description here

❌ Unnecessary sorts

The below will return the rows in order anyway but SQL Server apparently does not yet have the properties set to guarantee this and take advantage of it in the execution plan.

SELECT [value]
FROM GENERATE_SERIES(START=1, STOP=10)
ORDER BY [value]

enter image description here

RE: This last point Aaron Bertrand indicates that this is not a box currently ticked but that this may be forthcoming.

user_732260

edit: see Conrad's comment below.

Jeff Moden's answer is great ... but I find on Postgres that the Itzik method fails unless you remove the E32 row.

Slightly faster on postgres (40ms vs 100ms) is another method I found on here adapted for postgres:

WITH 
    E00 (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 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

As I am moving from SQL Server to Postgres world, may have missed a better way to do tally tables on that platform ... INTEGER()? SEQUENCE()?

user_5814140
Related