How to generate a range of numbers between two numbers?

sql

functions

performance

best-practices

byAnton Shumikhin·Nov 9, 2024

The fast way to generate a number series in SQL is to use a recursive Common Table Expression (CTE). Start with the lower limit and increment with each recursion until the upper limit is reached.

Example for numbers 1 to 10:

WITH RECURSIVE num_series AS (
  SELECT 1 AS num -- Start here, we begin with the number 1
  UNION ALL
  SELECT num + 1 FROM num_series WHERE num < 10 -- We keep Timmy (our recursion) counting till 10
)
SELECT * FROM num_series; -- So, Timmy, what did you get?

Just replace 1 and 10 to define your desired range. Make sure your database supports recursive queries for this approach to work.

Key Considerations and Variations

When we have to generate a number range to be used as part of join operations or as temporary data, certain aspects come into play:

If you don't want to maintain a fixed table, using VALUES with JOINs can help generate combinations efficiently.
For larger number ranges, it's better to use a row_number() window function since it offers better performance.
Larger data types like bigint are needed when the number range might exceed the integer limit.
If this logic will be reused across your application, it's a good idea to define a table-valued function.

Number Series Wrapper Function

For reusability, you can wrap the number generation logic within a function as shown below:

CREATE FUNCTION dbo.GenerateNumberSeries(@Min INT, @Max INT) 
RETURNS TABLE AS
RETURN (
  WITH RECURSIVE num_series AS (
    SELECT @Min AS num -- Again Timmy starts counting but this time from @Min
    UNION ALL
    SELECT num + 1 FROM num_series WHERE num < @Max -- And he doesn't stop till @Max
  )
  SELECT num FROM num_series -- Show us the count, Timmy!
)

You can call this function anywhere like this:

SELECT * FROM dbo.GenerateNumberSeries(1, 1000000); -- Yes, Timmy can count till a million!

Alternative Methods & Performance Implications

There are several other patterns for generating number ranges, and each pattern comes with its own trade-offs:

A cross join on master..spt_values can create a large number series quickly.
A predefined numbers table, also known as a tally table, can be used for quick sequence generation.
Recursive methods can slow down with large ranges, whereas a cross join with spt_values might be faster but less intuitive.
For generating time or date ranges, consider using built-in functions like DATEADD and DATEDIFF.

Potential Pitfalls & Edge Cases

Remember that your database's maximum recursion limit (MAXRECURSION) can truncate your number series. Ensure the limit suits your needs.
Make sure to account for inclusive upper ranges.
Be wary of off-by-one errors, a common pitfall in programming sequences.
If you expects series to contain millions of rows, you’ll need efficient code and sufficient memory to handle them.