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: