Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2008 3:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 4:03 PM
Points: 74, Visits: 449
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?


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....


Christopher Ford
Post #496142
Posted Wednesday, May 7, 2008 3:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 4:03 PM
Points: 74, Visits: 449
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. :)


Christopher Ford
Post #496146
Posted Wednesday, May 7, 2008 3:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #496152
Posted Wednesday, May 7, 2008 3:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.



Post #496160
Posted Wednesday, May 7, 2008 4:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Jeff,

Excellent Article!

It seems that you have written for me at the right time.:) 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.:)



karthik
Post #496161
Posted Wednesday, May 7, 2008 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:19 AM
Points: 2, Visits: 32
How about using recursion 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
Post #496162
Posted Wednesday, May 7, 2008 4:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 2,281, Visits: 4,227
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
Post #496163
Posted Wednesday, May 7, 2008 4:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196
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
Post #496166
Posted Wednesday, May 7, 2008 4:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:09 PM
Points: 217, Visits: 171
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.
Post #496167
Posted Wednesday, May 7, 2008 4:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 2,281, Visits: 4,227
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
Post #496182
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse