Working with temporary tally tables

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

SQL Server Development (2000)

Working with temporary tally tables

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-24 : 06:18:01

I occasionally need a tally table with values that are either sequential (1, 2, 3, ... , N) or date based (Jan 1, Jan 2, Jan3, ... )

Does anyone know a way to build a tally table as a temporary table inside a stored procedure and initialize it with a range of values without resorting to loops?

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-24 : 08:19:21

Of course this gets to be a lot of typing as N approaches infinity

, but ...


select
    a.i+b.i+c.i as i
into
    #tally
from
    (select 0 as i union select 1 as i union
     select 2 as i union select 3 as i) as a,
    (select 0 as i union select 4 as i union
     select 8 as i union select 12 as i) as b,
    (select 0 as i union select 16 as i union
     select 32 as i union select 48 as i) as c
order by
    a.i+b.i+c.i

I always keep a database called ToolBox or DBATools or something on each server. I always keep a large tally table in that database ....

Jay White
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-24 : 08:32:03

Thanks Jay - How's your coffee this morning?

I ought to be safe if I created a 10 year table beginning Jan 1, that's 3,650 rows. Not too bad... Lot of typing though. I guess I'll need to figure out how to do a loop / insert.

Looking at BOL, that would be

WHILE condition
BEGIN
-- Do the stuff
END

I imagine this is pretty slow stuff for SQL so building a table once and keeping it around is a good idea.

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-24 : 08:34:45


declare @i int, @startdate datetime
select @i = 0, @stardate = '1/1/2003'
while @i <= 3650
begin
    insert #tally
    select dateadd(dd,@i,@stardate)
    select @i = @i + 1
end

For that few rows, the loop will be pretty quick....

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-24 : 08:38:20

If you use a tally table like Page47 says you can then use a dateadd function to generate your dates.

DECLARE @StartDate DATETIME

SELECT @StartDate = '20030101'

SELECT DateAdd(d,Tally.i,@StartDate)
FROM Tally

dsdeming

479 Posts

Posted - 2003-04-24 : 09:29:40

I use a permanent number table in a utility database, and I don't know of any way other than looping to build one. However, it can be done fairly quickly:

SET NOCOUNT ON

CREATE TABLE #Tally
(
Sequence int IDENTITY( 1, 1 ),
PlaceHolder char( 1 ) NULL
)

-- This will populate #Tally with 2155 rows.
-- You could use any table or even none at all ( INSERT INTO #Tally SELECT '' ).
-- I chose Northwind..[Order Details] because it's shipped by MS and will
-- require only two passes through the loop.
INSERT INTO #Tally SELECT '' FROM Northwind..[Order Details]

WHILE @@ROWCOUNT < 4000
BEGIN
INSERT INTO #Tally SELECT PlaceHolder FROM #Tally
END

SELECT Sequence FROM #Tally

DROP TABLE #Tally

Just a thought...

Edited by - dsdeming on 04/24/2003 09:30:48

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 09:30:47

As Rob mentioned in another post (and I am quite upset I didn't mention it first, in that I consider myself "Mr. cross join"!) all you really need is a table of 1000 numbers and you can cross join to get as many numbers or dates as you will ever need.

select n1.n + 1000 * n2.n
from numbers n1
cross join numbers n2

make sure the numbers start with 0; otherwise, subtract 1 from n2.n.

Add as many cross joins as needed to increase the total numbers you can generate.

- Jeff

dsdeming

479 Posts

Posted - 2003-04-24 : 09:59:25

I like that CROSS JOIN trick. Never really considered that, but it works like a charm. Since I'm using a table with 8000 numbers ( an even number of data pages ), I can get 64M rows with a single CROSS JOIN. I can't imagine ever needing more than that.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-24 : 10:14:01

I suppose Mr. Cross-Join's (aka jsmith8858) solution would be as close to what I had imagined might be a no-loop solution.

I didn't get what Valter's was suggesting until I realized he's proposing using a Tally table of containing integers (1, 2, 3...) to produce a set of dates. Hmmm.. Suggests the reverse could be done too (select a set of integers from a table of dates), but that would be nuts.

Tho not many cries of the overhead in looping show in this thread, I've observed that long series of inline INSERT ... VALUE has been slow when run interactively in QA. I imagine (without benefit of test and measurement) that a loop of inserts would be dog-slow compared to a select on an in-place tally table, or a cross-join generated table.

Now, armed with these tools offered by the Yak warriors in this thread, I'm going to beat a dead horse and solve Peter's inventory problem exactly with a tally table of dates when I get some idle time this evening. That is, unless someone else beats me to it.

Thanks again.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-24 : 10:20:20

One more thing Jeff...

How would you build a cross join tally table that is ordered and specify a smaller subset of rows? Maybe a start and end?

Ahhh, I can probably do this..

SELECT A.index + 1000 * B.index AS index
FROM TallyHo A
CROSS JOIN TallyHo B
WHERE index BETWEEN @start AND @finish
ORDER BY index DESC

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 11:46:30

Sam -- you would actually have to write it like this:

SELECT A.index + 1000 * B.index AS index
FROM TallyHo A
CROSS JOIN TallyHo B
WHERE A.index + 1000 * b.index BETWEEN @start AND @finish
ORDER BY 1 DESC

A little math and an extra condition would make it more efficient, though:

SELECT A.index + 1000 * B.index AS index
FROM TallyHo A
CROSS JOIN TallyHo B
WHERE B.Index between (@start/1000) and (@finish/1000) AND
A.index + 1000 * b.index BETWEEN @start AND @finish

without the extra condition, it will need to do the entire cross join and filter down .... with the extra condition (which appears redundant), it should be MUCH more efficient. Especially for smaller ranges of data.

- Jeff

Edited by - jsmith8858 on 04/24/2003 11:51:17

Edited by - jsmith8858 on 04/24/2003 11:52:17

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-24 : 13:19:45

I've never seen the

ORDER BY 1 DESC

I suppose this is more efficent than

SELECT A.index + 1000 * B.index AS index
FROM TallyHo A
CROSS JOIN TallyHo B
WHERE A.index + 1000 * b.index BETWEEN @start AND @finish
ORDER BY A.index + 1000 * B.index DESC

Seems like ORDER BY 1 ought to have a better execution plan, but I see no difference.

Does the BY 1 syntax offer only easier reading?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 13:59:02

yeah, i think it's just easier to write. the expression must be derived the same way, no matter how you write it.

it MIGHT be different to look at:

Select Index
FROM
(
SELECT A.index + 1000 * B.index AS index
FROM TallyHo A
CROSS JOIN TallyHo B
) a
WHERE index BETWEEN @start AND @finish
ORDER BY index

but again, probably the same (or maybe worse) execution plan.... I like it, though, because it's a little more clear than repeating expressions over and over ....

However, defintely always add:

WHERE B.Index between (@start/1000) and (@finish/1000)

as I mentioned because that will really improve performance.

- Jeff

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-25 : 02:30:47

Side note on the ORDER BY 1 syntax... I tend to avoid using that primarily because I like the ease of reading just the ORDER BY statement and knowing what fields I'm sorting on instead of having to compare the numbers with the field list in the SELECT part. This is more significant when you are ordering on multiple fields, and especially when not in the listed order (e.g. "ORDER BY 4, 1, 3, 2"). Also, beware that if you change the list order in the SELECT, you have to change your ORDER BY numbers.

However, it sure is a nice trick when you're ordering by a calculated expression, as in this particular case.

------------------------------------------------------
The more you know, the more you know you don't know.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-25 : 19:25:14

I *think* that ORDER BY 1 also won't be supported in the future, but I can't remember where I saw that. I don't remember if it was standard ANSI SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources