SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The "Numbers" or "Tally" Table: What it is and how it replaces a loop.


The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Author
Message
Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 454
Hugo Kornelis (5/7/2008)
Shamshad Ali (5/7/2008)
Thank you all for your valuable replies. I would like to appreciate if some one make a scenario (simplest one mapped to my problem) and provide me solution/steps to be developed one by one. I need some sort of code + hints that may improve performance by removing Cursor, if possible.

I would like to appreciate help from Jeff and Christopher - Plz.


Not from me? Crying


haha, well...you do kind of sound crazy you know...have you read your blog lately? Downed one too many energy drinks I think. =)

EDIT:

(P.S. But it's all extremely great info Hugo...didn't want you to think I was insulting you)

Maybe I should put down the energy drinks.... w00t

Christopher Ford

Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 454
Shamshad,

Unfortunately, your question and explanation are still too broad to provide code or examples.

You mentioned you're calling a C# (ver. 3.5) DLL??

If you've started down that path, you might as well start using SQL Server Integration Services (SSIS) to do this nightly job that you have. This would be a much better solution than using a Tally table.

You can use SSIS to remove your cursor logic essentially doing everything in one pass, plus it will be in managed code.

You wouldn't need to migrate any current databases, just need to install Integration Services and the Business Intelligence Studio that comes with SQL 2005 to author your package. You could then invoke the custom package in .Net without running SQL 2005.

That's if you're bent on sticking with SQL 2000.

But, in short, my vote is no...I have no idea how to resolve your problem without upgrading you to a better Database System. Smile

Christopher Ford

ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6813 Visits: 1865
nice article jeff.

Here's another one with more uses of tally table.

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

"Keep Trying"
Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1446 Visits: 590
Well, very first i did thank to all, and ur included in that. But i saw some one is providing me a hint(looks like dreams comes true), so i put those two names coz they understand exactly wat my problem was and if someone know the problem then he post replies. So I would like if some spend time providing me that hint in practically proving the solution.


Thanks to every one once again.

I really apprecaite if some one take action and help me solve my problem (obviously first knowing the scenaio and making its solution)



Shamshad Ali.



karthik M
karthik M
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6937 Visits: 2586
Jeff,

Excellent Article!

It seems that you have written for me at the right time.Smile Really, It is very useful for me.

Because so far i have replaced more than 5 cursors and used Tally table in that places.

Thank You Sql teacher.Smile

karthik
chris-500268
chris-500268
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 39
How about using recursion Hehe to replace loops? (One of the great new functions in SQL2005)

No need for a tally or temporary tables.

Heres a params example:

DECLARE @Params varchar(8000)
SET @Params = '1,2,3,4,5,6,7,8,9,10';

WITH Params AS (
-- start with last item

SELECT REVERSE(SUBSTRING(REVERSE(@Params), 0, charindex(',', REVERSE(@Params)))) as [value], len(@Params) as start

UNION ALL

-- base case
SELECT SUBSTRING(@Params, 0, charindex(',', @Params)) as [value], charindex(',', @Params) + 1 as start

UNION ALL

-- recursive case
SELECT SUBSTRING(@Params, start, charindex(',', @Params, start) - start) as [value], charindex(',', @Params, start) + 1 as start
FROM Params
WHERE charindex(',', @Params, start) > 0


)
SELECT *
FROM Params
OPTION (MAXRECURSION 999); -- must be greater than max number of items
Carl Federl
Carl Federl
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7020 Visits: 4352
Another great article ! Thanks

The first reference for a tally table that I can recall is in "Guide to Sybase and SQL Server" by C. J. Date and D.McGoveran published in June of 1992.

Does anyone know of an earlier reference ?

SQL = Scarcely Qualifies as a Language
Mike DiRenzo
Mike DiRenzo
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 210
This is a super cool article. Let me repeat, this is a super cool article. What a refreshing read!

I look back on all the TSQL I have written using loops or cursors - arrrgh! Oh, and all the arguments/discussions on cursors, loops, variables of type table....

I am hooked on Tally tables. It is an extremely obvious in-your-face concept and I can't imagine why I haven't used this technique before.

Thanks again.

-Mike
Marius Els
Marius Els
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 180
Smooooth This is slick...... Great work Jeff!

This is a great example of implementing set-based programming, three thumbs up from me.
Now to apply this to problems going forward!

Thanks for the excellent article and insight into this subject.
Carl Federl
Carl Federl
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7020 Visits: 4352
From the article, you wrote " Tally table .. starting at 0 or 1 (mine start at 1)"
There are advantages for having the Tally table include 0 and the "Freight by Date" problem becomes easier if 0 is included.

The original SQL solution contains:

SELECT t.N-1+@DateStart AS ShippedDate
FROM dbo.Tally t
WHERE t.N-1+@DateStart <= @DateEnd


This SQL snippet has "N - 1", which derives the tally to start with 0. Wouldl it not be simplier to have the tally table already contain 0?

The original SQL solution has a problem that if the tally table does have a row for zero, the results are different (there is an extra day before the oldest shipment).

The original SQL solution has been modified to eliminate all variables so that the solution can be used in a view, does not assume that the tally table has 0 but allows the tally table to contain 0:

SELECT dates.ShippedDate
, COALESCE( SUM(o.Freight) , 0) AS TotalFreight
FROM dbo.Orders o
RIGHT OUTER JOIN
(SELECT t.N - 1 + Shipments.DateStart AS ShippedDate
FROM dbo.Tally t
JOIN (SELECT MIN(ShippedDate) AS DateStart
, MAX(ShippedDate) AS DateEnd
FROM dbo.Orders
) AS Shipments
ON t.N BETWEEN 1 AND ( Shipments.DateEnd - Shipments.DateStart + 1 )
) AS dates
ON o.ShippedDate = dates.ShippedDate
GROUP BY dates.ShippedDate
ORDER BY dates.ShippedDate



Here is revised SQL that assumes that the tally table has 0. The solution is slight simplier with the original SQL as comments and bold.

SELECT dates.ShippedDate
, COALESCE( SUM(o.Freight) , 0) AS TotalFreight
FROM dbo.Orders o
RIGHT OUTER JOIN
-- (SELECT t.N - 1 + Shipments.DateStart AS ShippedDate
(SELECT t.N + Shipments.DateStart AS ShippedDate
FROM dbo.Tally t
JOIN (SELECT MIN(ShippedDate) AS DateStart
, MAX(ShippedDate) AS DateEnd
FROM dbo.Orders
) AS Shipments
-- ON t.N BETWEEN 1 AND ( Shipments.DateEnd - Shipments.DateStart + 1 )
ON t.N BETWEEN 0 AND (Shipments.DateEnd - Shipments.DateStart )
) AS dates
ON o.ShippedDate = dates.ShippedDate
GROUP BY dates.ShippedDate
ORDER BY dates.ShippedDate



SQL = Scarcely Qualifies as a Language
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search