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.
Technorati Tags:No Tags
Thank you for providing the link to “someone else’s idea.” ;o)
[Takes quick look around] hey, fabulous site, and some great contents.
[Bookmarks]
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!
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’