Tally Tables: How to Use and Create? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tally Tables: How to Use and Create?

Author

Topic

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-24 : 05:19:57

I have seen references to "Tally Tables" here. As I understand it, they are tables with a single column of sequential numbers. They are used in JOINs to solve problems.

What are they good for? I have never used one. I'm hoping for a good explanation that will make me slap my forehead and shout out, "What a fool I am, all the wasted years!" (out of earshot of my wife, who would think I meant something else).

How big should the Tally Table be?

Also, a nice script for creating a useful Tally Table would be great.

Thanks in advance.

--
Timothy Chen Allen
[url]http://www.timallen.org[/url]

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-24 : 05:32:36

Hi!

One of my favorite topics

http://www.sqlteam.com/SearchResults.asp?SearchTerms=tally
Are some good uses for Tally tables, also check out (you probably already have) the recent challenge threads.

I often use it for parsing out CSV strings. Because varchars can't exceed 8000 I usually only create 8000 numbers in my sequence table.

Here is a script I use :

Create Table Sequence (seq int not null)
SET nocount on
declare @val int
select @val = 1
while @val <= 8000
begin
Insert into sequence values (@val)
select @val = @val + 1

end

Set nocount off

Damian

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-24 : 05:45:47

quote:
Originally posted by Merkin

One of my favorite topics

Thanks Merkin! I'm playing around with the CSV idea now.

--
TimothyAllen
[url]http://www.timallen.org[/url]

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-24 : 06:00:25

Or if you fancy something blazingly fast for 100,000 records, try the new and improved set-based approach

:


create table #numbers
(
	num int
)

go
insert into #numbers
select 0
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
go

select (e.num*10000) + num10000 as num100000 from
	(select (d.num*1000) + num1000 as num10000 from
		(select (c.num*100) + num100 as num1000 from
			(select (b.num*10) + a.num as num100 from #numbers a cross join #numbers b) z
			cross join #numbers c) y
		cross join #numbers d) x
	cross join #numbers e

order by 1
go

drop table #numbers
go

Beats the iterative approach by a mile!

Owais

Please excuse my driving, I am reloading.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-24 : 06:04:42

You only need to create it once though. Shaving milliseconds off the creation time isn't a huge deal.

Damian

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-12-04 : 18:41:58

quote:
Originally posted by Merkin

You only need to create it once though. Shaving milliseconds off the creation time isn't a huge deal.

Damian

I realize that this particular post is about 3 days older than dirt but thought I'd reply anyway.

While it certainly may not matter to a permanent Tally Table how it was created, the CROSS JOIN technique is hugely more performant percentage wise and will scale very, very well. The point isn't so much how to create a Tally Table quickly... the point is how to avoid the loop in any similar code where performance will be more important.

I also think it ironic that anyone building a Tally Table so they can enjoy the performance it offers, would teach others to use a WHILE loop to build it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources