Introduction

"You must unlearn what you have learned" - Yoda

What exactly is a numbers table and more importantly, what can it do for you? To put it simply, a numbers table is

cheap (read: "free") tool that should be part of every SQL developer's toolkit. In its simplest form, a

numbers table is a table with one INT column. That single column, which also serves as

the primary key and clustered index, contains the natural (or "counting") numbers starting with 1 and going

up to some specified upper limit. We'll begin by describing how to create a numbers table, and then move on to a

discussion of why they are so useful. As always, I'll throw in some sample code (for SQL Server 2000 and

SQL Server 2005) so you can play along at home.

Make Your Own Numbers Table!

Let me start by saying that every database should have its own numbers table. They're very useful (as we'll

see later), and extremely easy to create. We'll start with the creation of the numbers table. This will work in

SQL Server 2000 or SQL Server 2005:

-- Create the numbers table
CREATE TABLE dbo.Numbers (Num INT NOT NULL PRIMARY KEY CLUSTERED);
GO

Well that was simple enough. Now comes the fun part - populating it. There are lots of methods to populate the

numbers table, but we'll start with the most obvious solution first. This method is a simple

WHILE loop that will populate our numbers table one row at a time:

DECLARE @i INT;
SELECT @i = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.Numbers(Num) VALUES (@i);
    SELECT @i = @i + 1;
END;

This simple procedural solution works on both SQL 2000 and SQL 2005. There are numerous other clever ways to

create a numbers table. Here's one of my personal favorites (thanks to Jeff Moden and all others who posted it

for public consumption). This method uses the Transact-SQL SELECT INTO

statement and IDENTITY function to simultaneously create and populate

the table, so we have to drop the previously created numbers table. This example works on SQL 2000 or SQL 2005:

-- Be sure to drop the numbers table if it exists
DROP TABLE dbo.Numbers;
GO
-- Now re-create it and fill it with sequential numbers starting at 1
SELECT TOP 10000 IDENTITY(INT,1,1) AS Num
INTO dbo.Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num);
GO

Notice that this set-based method offers a significant speed improvement over the WHILE loop version. This simple

query relies on the fact that a self-CROSS JOIN creates an exponential result set. If your

master.INFORMATION_SCHEMA.COLUMNS table has over 400 rows in it (not unreasonable), the CROSS JOIN

will generate over 160,000 rows. If you need more just slap another CROSS JOIN in there. We're using the

TOP keyword on the SELECT statement to limit the results to 10,000 rows for our purposes though.

Feel free to populate your numbers table with as many numbers as you need up to the upper limit for an INT,

which happens to be 2,147,483,647.

Now that we have a numbers table it's time to put it to work solving some problems.

The Numbers Table in Action

The power of the numbers table is that it can be used to convert procedural solutions to set-based solutions.

Consider the ubiquitous comma-delimited string-split function. As before we'll start with the procedural version

which basically consists of a WHILE loop. This code works on

SQL 2000 and SQL 2005:

CREATE FUNCTION dbo.fnProceduralSplit(@string VARCHAR(8000))
RETURNS @ret TABLE (
    Num INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    String VARCHAR(255))
AS
BEGIN
    DECLARE @i INT, @j INT;
    SELECT @i = 1;
    SELECT @j = CHARINDEX(',', @string);
    WHILE (@j > 0)
    BEGIN
        INSERT INTO @ret (String)
        VALUES (SUBSTRING(@string, @i, @j - @i));
        SELECT @i = @j + 1;
        SELECT @j = CHARINDEX(',', @string, @i);
    END;
    INSERT INTO @ret (String)
    VALUES (SUBSTRING(@string, @i, LEN(@string) - @i + 1));
    RETURN;
END
GO

All this code does is loop through the string passed in as a parameter looking for commas. It breaks out the

strings between any commas it finds. It's probably about as efficient as you can get for procedural code. So

now let's see what the equivalent function looks like in a set-based version on SQL 2000 and SQL 2005.

In this version we'll let SQL Server do all the heavy lifting. We'll use an inline table-valued function

for performance, and we'll return the number representing the position of each comma in the list to keep the

relative ordering of strings in the list:

CREATE FUNCTION dbo.fnSetSplit (@String VARCHAR(8000))
RETURNS TABLE
AS
RETURN (
    SELECT Num,
        SUBSTRING(@String,
            CASE Num
                WHEN 1 THEN 1
                ELSE Num + 1
            END,
            CASE CHARINDEX(',', @String, Num + 1)
                WHEN 0 THEN LEN(@String) - Num + 1
                ELSE CHARINDEX(',', @String, Num + 1) - Num -
                    CASE 
                        WHEN Num > 1 THEN 1
                        ELSE 0
                    END
               END
            ) AS String
    FROM dbo.Numbers
    WHERE Num <= LEN(@String)
        AND (SUBSTRING(@String, Num, 1) = ','
            OR Num = 1)
)

You can test the functionality of your new UDF's with queries like the following:

SELECT * FROM dbo.fnSetSplit ('Jack,Jill,Hill,Water');
SELECT * FROM dbo.fnSetSplit ('Alexander The Great');
SELECT * FROM dbo.fnSetSplit ('Red,Green,Blue');
SELECT * FROM dbo.fnProceduralSplit ('Army,Navy,Air Force,Marines');
SELECT * FROM dbo.fnProceduralSplit ('Washington,Jefferson,Adams');
SELECT * FROM dbo.fnProceduralSplit ('1,2,3,4,5,6,7,8');

On an individual basis, for single queries like those above you might not notice a large difference in performance,

but for thousands of such queries in a short time period, the difference can be significant. I ran several rounds

of comparisons splitting 100,000 randomly generated comma-separated lists of "words"^*. Here

are the averages:

Notes:

^*The "words" were acutally randomly generated strings from 1 to 20 characters in length.

^**In SQL 2005, using the CROSS APPLY operator reduced the time of the

set-based test to 27,296 ms.

Of course performance may vary on your system, but the results I got show a definite improvement when using

the set-based version over the procedural version.

Here Comes the Second Example

OK, still not convinced? Then let's try another example. Here we'll eliminate duplicate side-by-side letters from

a string. This is a commonly performed operation in phonetic encoding and approximate search routines, and here

we'll do it in a simple set-based UDF that will work on SQL 2000 or 2005:

CREATE FUNCTION dbo.fnReduceDupes(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Result VARCHAR(8000);
    SELECT @Result = @string;
    SELECT @Result =
        STUFF(@Result, Num, 1,
            CASE SUBSTRING(@Result, Num, 1)
                WHEN SUBSTRING(@Result, Num + 1, 1) THEN '!'
                ELSE SUBSTRING(@Result, Num, 1)
            END)
    FROM dbo.Numbers
    WHERE Num <= LEN(@Result);
    SELECT @Result = REPLACE(@Result, '!', '');
    RETURN @Result;
END

This UDF reduces sequences of side-by-side duplicate characters to a single character. It does this by first

replacing all but the last of any sequence of side-by-side duplicate characters with an exclamation point

('!', although any character could be used.) It then uses the Transact-SQL REPLACE function to remove

all exclamation points from the input string. Again, this function would be a perfect candidate for

a WHILE loop, but the numbers table lets us tell SQL Server to do the hard work for us. Here are some samples

to try:

SELECT dbo.fnReduceDupes ('AAAABBBBBCCDDDDEEEEFFGGGGHIIIIJJKK');
SELECT dbo.fnReduceDupes('HHHHHIIIIII TTTTHHHEEEERRRRRRREEEE');

One Last Time

OK, so we can see the usefulness of a numbers table in creating set-based position-dependent string functions. But

all the world is not a string; and often well need to apply business logic that has nothing to do with string

manipulation.

Let's consider the SQL 2000 Northwind Database for a moment. This database has a very simple "inventory"

system of sorts -- the kind you might create for a very basic Webstore. Basically the dbo.Products

table has a single column named UnitsInStock. This is your inventory. I say it's very

basic because it lacks any lot/bin or tracking information. The items and quantities of those items ordered are

stored in the ProductID and Quantity columns of the

dbo.[Order Details] table.

The problem is this: we need to match up the items in inventory to specific customer orders. The only real issue

we might face is the total quantity of a particular product on order totals more than the total quantity of that

product in inventory. I.e., we've sold or promised more than we have on hand. In a more detailed inventory

tracking system (such as the SQL 2005 AdventureWorks shelf/bin inventory system), we are looking at more potential

issues (i.e., a customer orders more of a product than is contained in one bin so we have to pull from multiple

bins to fill one order, assigning items from bins to customer orders, etc.)

A row-by-row cursor might seem like the obvious solution for this problem. After all, we need to assign each item

we have in inventory to a customer order detail row. Also we need to keep track of the inventory quantity to make

sure we don't over-commit and try to ship more product than we have on hand. Or do we?

Here I'll present a SQL 2000 Northwind solution to this problem:

SELECT Order_Items.OrderID, Product_Inventory.ProductID,
    COUNT(*) AS ItemsFromInventory
FROM
(
    SELECT ProductID, Num
    FROM dbo.Numbers n
    INNER JOIN dbo.[Products] p
    ON n.Num BETWEEN 1 AND p.UnitsInStock
) Product_Inventory
INNER JOIN
(
    SELECT OrderID, ProductID,
    (
        SELECT COALESCE(SUM(o1.Quantity),0)
        FROM dbo.[Order Details] o1
        WHERE o1.ProductID = o.ProductID
            AND o1.OrderID < o.OrderID
        ) + n.Num AS Num
    FROM dbo.Numbers n
    INNER JOIN dbo.[Order Details] o
        ON n.Num BETWEEN 1 AND o.Quantity
) Order_Items
ON Product_Inventory.ProductID = Order_Items.ProductID
AND Product_Inventory.Num = Order_Items.Num
GROUP BY Order_Items.OrderID, Product_Inventory.ProductID
ORDER BY Order_Items.OrderID, Product_Inventory.ProductID;

OK, so what just happened here?

To begin with, we joined each product we have in inventory to the numbers table using

ON n.Num BETWEEN 1

AND p.UnitsInStock. What this does is individually number each product item

ordered. By way of example, if we have two of Product ID 998 in stock and three of Product ID 999 in stock, the

Product_Inventory subquery will generate the following result:

Next we assigned a number to each item on each order. This is the Order_Items subquery.

It's more complex than the Product_Inventory query because we have to account for both

ProductID and OrderID. The complexity comes because the

items ordered and their quantities are spread out over several rows on several different orders. But we want the

count to go from 1...n where n is the total quantity of a single ordered item across all orders.

Functionally we're just replicating SQL 2005's ROW_NUMBER functionality, which will

do this exact same thing for us when the optional PARTITION BY clause is specified.

We INNER JOIN these two subqueries by their

ProductID and Num columns, which effectively assigns one

item from inventory to one item on each order. We don't have to worry about assigning more items than we have in

inventory since the INNER JOINs in the subqueries won't let us assign

more of an item than we have in inventory. After assigning each inventory item to an order detail item, we group

the results. The COUNT(*) of each group gives us the

total number of each item from inventory that was assigned to a given order.

The SQL 2005 version is similar, although the ROW_NUMBER function and

CTE's will probably make the job easier. Of course, as mentioned, the AdventureWorks sample database uses a

shelf/bin inventory system that's also slightly more complex. I'll leave the SQL 2005 version of this query as

an exercise for the reader.

Conclusions

Numbers tables are an extremely useful tool for writing efficient set-based code to replace cursors and other

procedural constructs.