Number generation using tally tables in SQL Server

In database SQL Server programming we will have requirements to generate numbers of sequence to use along with other tables. In this scenario we can follow many of below steps to generate.

Example if we want to generate numbers 1 to 100.

1. Using system tables

We can use some of the system tables to generate numbers as we require. For that example we can use master..spt_values tables and use row_number() to generate numbers as below:

Select top (100) Num = Row_Number() over(order by (Select NULL))
      from master..spt_values s1, master..spt_values s2

Advantages with this approach is that it is easy and quick to write
Disadvantages with this approach is that we are unnecessarily using System tables for number generation.

2. Using Values and Common Table Expressions

By using ‘Values’ this approach we are going to generate numbers as below:

;With cte1 as 
  ( Select * from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(n) )
  ,Cte2 as ( select c1.* from cte1 c1, cte1 c2, cte1 c3)
  Select top(100) Num = row_number() over (order by (select null)) 
      from cte2

This approach is normally used across as we can generate required numbers

3. Using Recursive Common Table Expressions

This approach as good as looping but thru recursive Common Table expressions.

;With Cte_numbers as (
  Select 1 as Num				        --Start number
    Union all
  Select Num + 1 from Cte_numbers where Num < 100	--End number
  )
  Select * from Cte_numbers

Based on the scenarios of requirements we can use any of these approaches.

Demo for these approaches are here.

Some user scenarios are as below:

1. StackOverflow Examples with my answers as in approach 2
2. StackOverflow Examples with my answers as in approach 1

Advertisements