Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

USE [Auxiliary]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE  [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
 ([n]  ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)
 
INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO



 Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html

Auxiliary CTE of numbers - SQL

Following on from the previous auxilary CTE of numbers, http://sqlreportingservicescrystalreports.blogspot.com/2009/12/create-common-table-expression-of.html
I realised after reading T-SQL Querying by Itzik Ben-Gan that he's created a much more efficient way of doing it:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;





taken from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139

Create Common Table Expression of numbers - SQL

To create a Common Table Expression (CTE) of numbers for use in queries:


DECLARE @n AS INT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT * FROM Nums