Easily produce and use a table of numbers

A numbers table is a versatile tool that can be used to synthesize ranges of various kinds of data. For instance, you can write queries against a numbers table to identify gaps or breaks in a sequence. We’ll see this in a moment.

First let’s generate the numbers table.

WITH CTE
AS
(
    -- Recursive CTE
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM CTE
    WHERE n < 100 -- The total number of rows to output
)
SELECT n -- Change this to a SELECT ... INTO to persist the results in a table
--INTO dbo.Numbers
FROM CTE;

The output is a single column of numbers from 1 to 100.

| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
 ...

I used a recursive common table expression to generate the column of numbers. There are alternative ways of producing these results using e.g. a CROSS JOIN. But I find the CTE to be the most concise approach.

So what can you do with a numbers table? Here’s a couple examples.

Let’s say you need to generate a list of all 256 possible values for the 4th octet of an IP range.

WITH CTE
AS
(
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM CTE
    WHERE n < 256
)
SELECT TOP 256 '192.168.0.' + CONVERT(VARCHAR(3), n - 1)
FROM CTE
OPTION (MAXRECURSION 0);
-- By default SQL Server will limit recursion to 100 lines.
-- The MAXRECURSION 0 option disables the limit.

| IPAddresses |
|-------------|
| 192.168.0.0 |
| 192.168.0.1 |
| 192.168.0.2 |
| 192.168.0.3 |
| 192.168.0.4 |
| 192.168.0.5 |
| 192.168.0.6 |
| 192.168.0.7 |
| 192.168.0.8 |
| 192.168.0.9 |
 ...

What if you need to see a list of all ID numbers not present in a table? Here’s how you could use a numbers table to find that information.

USE AdventureWorks2017;
GO

WITH CTE
AS
(
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM CTE
    WHERE n < 3000
)
SELECT n AS BusinessEntityID
FROM CTE
WHERE n NOT IN (SELECT BusinessEntityID from Sales.PersonCreditCard)
	AND n < (SELECT MAX(BusinessEntityID) FROM Sales.PersonCreditCard)
ORDER BY BusinessEntityID
OPTION (MAXRECURSION 0);

| BusinessEntityID |
|------------------|
|              ... |
|              360 |
|              362 |
|              364 |
|              366 |
|              368 |
|              370 |
|              372 |
|              374 |
|              376 |
|              378 |
|              380 |
|              382 |
|              ... |
 
-- 1658 Rows