Home Uncategorized You REQUIRE a Numbers table!

    You REQUIRE a Numbers table!

    1918
    7

    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:

    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.

    Previous articlePerformance: ISNULL vs. COALESCE
    Next articleControlling Stored Procedure Caching with … Dyanmic SQL?!?
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    7 COMMENTS

    1. 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

    2. 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

    3. 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.

    4. There’s an easier way if all you want is a sequence of numbers between 0 and 2047, which is to use the spt_values table…
      SELECT DISTINCT number
      FROM master..spt_values
      WHERE type = ‘P’
      It’s a bit hacky, but it gets the job done.

    5. @Thomas
      LOL, I guess you didn’t bother to read the code in the post above before commenting?
      –Adam

    Comments are closed.