Here is a quick look at number sequences, which are a brilliant (and fairly database neutral) method to create numbers (and rows) to build datasets from.

While working on the dates articles, I used number sequences a lot and thought they were worth coming back to.

What is a number sequence

A number sequence can be any series of numbers in a pattern. Here I’m talking about incrementing the number by 1 in each row. In many DBMS environments (SQL Server included) there is a row number metadata attribute which takes care of this. This method gives developers the opportunity to create rows from no existing data, and apply calculations to manipulate values.

For this sequence, I create a dataset with numbers 0 - 9, and then cartesian join that dataset to itself n times. This will create all numbers between 0 and (10ⁿ)-1. So, join the number table 3 times times for 0 - 999. See the below code example, I’ve made it deliberately verbose in nature to demonstrate the idea.

SQL Server (and MySQL with minimal changes)

--------------------------------------------------------------------------------
-- First get a recurring list of numbers
--------------------------------------------------------------------------------
WITH num_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
)
SELECT t1.a
  FROM num_tbl t1
;

--------------------------------------------------------------------------------
-- Create a cartesian product
--------------------------------------------------------------------------------
WITH num_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
)
SELECT t1.a AS t1a,
       t2.a AS t2a
 FROM num_tbl t1
  CROSS JOIN num_tbl t2
;

--------------------------------------------------------------------------------
-- Create a cartesian product (3 digits with calculation)
--------------------------------------------------------------------------------
WITH num_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
)
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t1.a + (10 * t2.a) + (100 * t3.a)) AS concatenate_calculation
  FROM num_tbl t1
  CROSS JOIN num_tbl t2
  CROSS JOIN num_tbl t3
;

--------------------------------------------------------------------------------
-- Create Date list
--------------------------------------------------------------------------------
WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t1.a + (10 * t2.a) + (100 * t3.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
SELECT t1a,
       t2a,
       t3a,
       concatenate_calculation,
       CAST(GetDate() AS DATE)                             AS todays_date_SQL_Server,
       CAST(GetDate() + (concatenate_calculation) AS DATE) AS calculated_date_range_SQL_Server
     /*CURDATE()                                           AS todays_date_MySQL,
       CAST(CURDATE() + (concatenate_calculation) AS DATE) AS calculated_date_range_MySQL */
  FROM number_sequences
 ORDER BY concatenate_calculation
;

Oracle

The method is the same, the key difference for oracle is the use of dual as a virtual table.

WITH number_tbl AS (
SELECT 0 AS a FROM dual UNION ALL
SELECT 1 AS a FROM dual UNION ALL
SELECT 2 AS a FROM dual UNION ALL
SELECT 3 AS a FROM dual UNION ALL
SELECT 4 AS a FROM dual UNION ALL
SELECT 5 AS a FROM dual UNION ALL
SELECT 6 AS a FROM dual UNION ALL
SELECT 7 AS a FROM dual UNION ALL
SELECT 8 AS a FROM dual UNION ALL
SELECT 9 AS a FROM dual
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t1.a + (10 * t2.a) + (100 * t3.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
SELECT t1a,
       t2a,
       t3a,
       concatenate_calculation,
     TRUNC(SysDate)                           AS todays_date_Oracle,
     TRUNC(SysDate) + concatenate_calculation AS calculated_date_range_Oracle
  FROM number_sequences
 ORDER BY concatenate_calculation
;

Manipulating the resultset

The resultset from this method is very simple, but can be used anywhere we want to create a sequence of numbers/rows:

• Date range table
• Medium/large dataset for testing