Xaprb

Stay curious!

The integers table

with 4 comments

For the sake of adding cross-references and my own analysis, I’m going to replicate part of someone else’s idea. An integers table can be used to create any desired sequence of numbers, and the idea can be extended to other data types as well. It’s easy to use a mutex table as an integers table, so there’s no need for two tables of the values. You can even create tables with characters, use integers with date functions to generate a range of dates, and so forth. Here is the canonical integers table, and the canonical select from it:

create table integers(i int unsigned not null);
insert into integers(i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select (hundreds.i * 100) + (tens.i * 10) + units.i as iii
from integers as units
    cross join integers as tens
    cross join integers as hundreds;

A very useful technique indeed.

I use the integers table in a number of ways in my posts about SQL. Sometimes you’ll see me refer to it as the “numbers” table too.

Written by Xaprb

December 7th, 2005 at 11:34 am

Posted in SQL

« How to find missing values in a sequence with SQL
How to use extended properties as documentation with sp_showdoc »

4 Responses to 'The integers table'

Subscribe to comments with RSS or TrackBack to 'The integers table'.

  1. Thank you for providing the link to “someone else’s idea.” ;o)

    [Takes quick look around] hey, fabulous site, and some great contents.

    [Bookmarks]

    rudy

    4 Feb 06 at 3:03 pm

  2. Ah yes, I see you have written articles about this topic too! I forget now who I originally got the idea from, but it’s out there in a number of places (no pun intended). Thanks for the compliment!

    Xaprb

    5 Aug 06 at 5:45 pm

  3. I came accross this blog trying to learn more about Tally Tables, anyway here something else I found that does what your showing above, but faster if your interested. (Ran in about 1/9th the time for 100,000 numbers) Cool Blog, hope that book is comming well.

    DECLARE @StartTime DATETIME –Timer to measure total
    SET @StartTime = GETDATE() –Start the timer

    SELECT TOP 100000
    IDENTITY(INT,1,1) AS N
    INTO dbo.Tally
    FROM Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

    Select * From Tally

    Drop table Tally
    SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) ‘ Milliseconds duration’

    Jeremy West

    7 May 08 at 12:59 pm

  4. Jeremy, there isn’t an identity in mysql, is there?

    Xaprb, am not seeing how this plus the code in your page on char-dump work together since your integer table ends up being 0-9. There’s no doubt a better way, but I created a larger integer table via a temporary 0-9 table:

    create table Uintegers(i int unsigned not null);
    create table integers(i int unsigned not null);
    insert into Uintegers(i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

    (like yours so far)
    Then, use this table of 9 as workspace to create the needed collection of integers (0-999):

    insert into integers (
    select (hundreds.i * 100) + (tens.i * 10) + units.i as iii
    from Uintegers as units
    cross join Uintegers as tens
    cross join Uintegers as hundreds );

    Then, to clean up:

    drop table Uintegers;

    sage

    29 Sep 08 at 7:36 pm

Leave a Reply