Tuesday, September 05, 2006

A fun way to populate a numbers table

Numbers tables are a useful tool in the arsenal of any serious SQL server developer. The main advantage is they often allow you to write a set based solution for what would normally require a procedural solution. For a bit of background and some examples, check out this article on sqlservercentral.com.

There are a number of ways to populate a numbers table. A colleague of mine recently shared the method below and I think it's the fastest and slickest method around. (please note that this method adds a zero record)

Enjoy!

SELECT
(i4.Num*1000 + i3.Num*100 + i2.Num*10 + i1.Num) AS Num
into dbo.Numbers
FROM (
SELECT 0 AS Num UNION ALL
SELECT 1 AS Num UNION ALL
SELECT 2 AS Num UNION ALL
SELECT 3 AS Num UNION ALL
SELECT 4 AS Num UNION ALL
SELECT 5 AS Num UNION ALL
SELECT 6 AS Num UNION ALL
SELECT 7 AS Num UNION ALL
SELECT 8 AS Num UNION ALL
SELECT 9 AS Num
) AS i1
CROSS JOIN
(
SELECT 0 AS Num UNION ALL
SELECT 1 AS Num UNION ALL
SELECT 2 AS Num UNION ALL
SELECT 3 AS Num UNION ALL
SELECT 4 AS Num UNION ALL
SELECT 5 AS Num UNION ALL
SELECT 6 AS Num UNION ALL
SELECT 7 AS Num UNION ALL
SELECT 8 AS Num UNION ALL
SELECT 9 AS Num
) AS i2
CROSS JOIN
(
SELECT 0 AS Num UNION ALL
SELECT 1 AS Num UNION ALL
SELECT 2 AS Num UNION ALL
SELECT 3 AS Num UNION ALL
SELECT 4 AS Num UNION ALL
SELECT 5 AS Num UNION ALL
SELECT 6 AS Num UNION ALL
SELECT 7 AS Num UNION ALL
SELECT 8 AS Num UNION ALL
SELECT 9 AS Num
) AS i3
CROSS JOIN
(
SELECT 0 AS Num UNION ALL
SELECT 1 AS Num UNION ALL
SELECT 2 AS Num UNION ALL
SELECT 3 AS Num UNION ALL
SELECT 4 AS Num UNION ALL
SELECT 5 AS Num UNION ALL
SELECT 6 AS Num UNION ALL
SELECT 7 AS Num UNION ALL
SELECT 8 AS Num UNION ALL
SELECT 9 AS Num
) AS i4
ORDER BY Num