Generating a list of numbers in T-SQL
I wanted to create a list of numbers so I could cross join them to another set of data to generate some test data quickly. There are ways to generate numbers using a CTE (Common Table Expression) or if you’re in a real hurry you can make us of a fairly undocumented table called master..spt_values. Seeing as this table isn’t well documented don’t go using it for production code since it might change but for quick one-off tasks it seems fine.
The following SQL creates a list of numbers from 0 to 2047 :
select [number] from master..spt_values where type = 'P'