THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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.


Published Wednesday, July 12, 2006 10:18 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited

January 25, 2009 2:44 PM
 

Adam Machanic said:

I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums:

January 25, 2009 2:44 PM
 

Adam Machanic said:

Continuing in my series of things you should probably not do in SQL Server but sometimes have to , I'm

January 25, 2009 2:45 PM
 

Adam Machanic said:

Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some

January 25, 2009 2:53 PM
 

Adam Machanic said:

Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting.

January 25, 2009 2:54 PM
 

Chen Noam said:

Great article, very good performance.

Thanks,

Chen

November 29, 2010 8:00 PM
 

Joel Theophanes said:

Another number table generator. Not as compact as Adam's, but easy to see how many numbers are generated and doesn't rely on a system table for the initial number list.

;with t as (

select 0 as Num union

select 1 union

select 2 union

select 3 union

select 4 union

select 5 union

select 6 union

select 7 union

select 8 union

select 9

)

select (10000 * t10000.Num) + (1000 * t1000.Num) + (100 * t100.Num) + (10 * t10.Num) + t1.Num as Number

from t t1

cross join t t10

cross join t t100

cross join t t1000

cross join t t10000

order by Number

October 11, 2011 1:13 PM
 

Adam Machanic said:

Nice, Joel. I use the following technique these days, which I learned a while back from Itzik Ben-Gan:

---

DECLARE @number_of_numbers INT = 100000;

;WITH

a AS (SELECT 1 AS i UNION ALL SELECT 1),

b AS (SELECT 1 AS i FROM a AS x, a AS y),

c AS (SELECT 1 AS i FROM b AS x, b AS y),

d AS (SELECT 1 AS i FROM c AS x, c AS y),

e AS (SELECT 1 AS i FROM d AS x, d AS y),

f AS (SELECT 1 AS i FROM e AS x, e AS y),

numbers AS

(

SELECT TOP(@number_of_numbers)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number

FROM f

)

SELECT *

FROM numbers

---

October 16, 2011 9:50 PM
 

joining to a numbers table said:

Hi,

I'd like to joint to a numbers table using the row_number value.  WITHOUT using a CTE.  I'm interested to see if it's faster and possible without CTE.  I can't think of a way to join the ROW_NEMBER Value to the Number table.

Thanks for reading.

January 17, 2013 5:34 PM
 

Adam Machanic said:

Sure, you can do it without a CTE. Use a derived table.

--Adam

January 19, 2013 4:10 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement