TSQL -> Create tally table ("Numbers" table) in TSQL

Tally table ("Numbers" table) is extremly usefull for a number of SQL queries solutions. Following script creates table that will store 100000 sequential numbers.

-- -------- create sequential numbers table ----------

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type in (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;

CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);


More about TSQL technuque for creating tally table:
Tally Table - Number Table. Script on databasechallenges.com

Create function that returns tally table of a given size

More about uses of tally (numbers) table:
Split character string to words using tally table


sqlexamples.info