posted on Friday, December 17, 2004 10:01 AM
by
amachanic
You REQUIRE a Numbers table!
Looking at my list of upcoming articles, I keep seeing the same theme repeated over and over. A sequence table of Numbers.
Numbers tables are truly invaluable. I use them all of the time for
string manipulation, simulating window functions, populating test
tables with lots of data, eliminating cursor logic, and many other
tasks that would be incredibly difficult without them.
Is using a table of numbers a hack, as I've seen some people
claim? No. Show me another way to efficiently do all of the things a
numbers table can. Does it waste space? No. The script below will use
up around 900 KB of disk space in each database. That's absolutely
nothing. You'll end up getting millions, maybe billions of times the
disk space investment back in terms of ease of development and time
saved.
So henceforth, I will assume in this blog that everyone reading
has a Numbers table. And I will politely link to this article as a
gentle reminder. But I want you to open Query Analyzer right now and
use the following script:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number)
WITH FILLFACTOR = 100
)
INSERT INTO Numbers
SELECT
(a.Number * 256) + b.Number AS Number
FROM
(
SELECT number
FROM master..spt_values
WHERE
type = 'P'
AND number <= 255
) a (Number),
(
SELECT number
FROM master..spt_values
WHERE
type = 'P'
AND number <= 255
) b (Number)
GO
There. Now you automatically have a Numbers table in every database
you create, populated with every number between 0 and 65535. That's big
enough for most tasks. If you need more numbers, just insert more! It's
fun and easy! And trust me, you'll use this table. A lot. And you'll
thank me one day, probably by sending me lots of gifts, as a very small
token of your appreciation.
But in the meantime, here are two links with more information on using a numbers table:
ASP FAQ #2516, "Why should I consider using an auxiliary numbers table?"
Fun with numbers in Transact-SQL queries
Update, December 1, 2005: Fixed the insert script for SQL Server 2005; master..spt_values now has a lot more numbers (0-2048 instead of 0-255) so as-is the script was failing. It should work properly now.