Create and use number tables in T-SQL

How to Create and Use A Numbers Table
A Numbers table is simply a table that holds a single integer column with each positive integer number in the range from the number 1 to the maximum value of an integer 2,147,483,647. Creating a Numbers table is really simple to do and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:
Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e

-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)