'N' number of usage - Tally Table

Question

Post reply

'N' number of usage - Tally Table

karthik M

One Orange Chip

Points: 29139
More actions
October 1, 2008 at 4:25 am

#193362

All,
I hope all of you know about Mr Jeff's Tally table. I also believe your people knows the usage of Tally table. Here i have listed out the usage of Tally table(so far i implemented that table in my work)
1) Splitting delimited values
2) Finding Palindrome
3) Date/Months generation
4) Displaying pyramid format output
5) Identifying the duplicate letters from a string
is there any other points that i missed out ? I hope the list should grow further. Also is there any other important usage that i missed out ?
karthik

Viewing 15 posts - 1 through 15 (of 66 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Fuzzy dedupe:

[font="Courier New"]-- Self fuzzy match

SELECT t.VendorID, r.VendorID AS Dupe, COUNT(*) AS TokenCount

FROM FuzzyInputFile r

INNER JOIN [Numbers] n ON n.number < LEN(r.VendorName)

INNER JOIN FuzzyInputFile t ON t.VendorID <> r.VendorID

AND CHARINDEX(SUBSTRING(r.VendorName, n.number, 3), t.VendorName) > 0 -- 3 is token size, tune to data

WHERE n.number < 30 -- VendorName column length

GROUP BY t.VendorID, r.VendorID, t.VendorName

HAVING COUNT(*) / CAST(LEN(t.VendorName) AS NUMERIC (5,2)) > 0.4 -- Cutoff: tune to data

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Any more usages apart from that chris & mine mentioned?

karthik

Dugi SSCoach Points: 17998 More actions · Answer 3

As I understand you are listing the things what can you do with Tally Table!?

In my experience I used Tally table to find out the missing numbers in my Identity column!

so from me is another one:

6) Finding missing numbers in Identity Column

Another thing here it would be better if this topic goes as Article with Title "All what can you do with Tally Table ( Jeff's Table)" and I'm sure that Steve can do it but first of all we must put the things what we have done with tally table!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Concur...

If it comes as an article it would be useful for lot of folks.

karthik

bobhovious SSC Enthusiast Points: 188 More actions · Answer 5

In addition to dates, it's useful for generating any range of numbers, such as invoice numbers, where you might be looking for gaps. If I wanted to test the range A10000-A19999 in the invoice table for gaps, I could use the following:

Select 'A'+cast(10000+N-1 as varchar(10)) as InvNo

from tally t

left join invoice i on i.invno = 'A'+cast(10000+N-1 as varchar(10))

where N <= 10000

and i.invno is null

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

October 7, 2008 at 1:14 am

#881458

Anybody want to add some more points ?

karthik

Dugi SSCoach Points: 17998 More actions · Answer 7

Till now we have 7 + fuzzy dedupe from Chris Morris ...ooops it seems 8!

1) Splitting delimited values

2) Finding Palindrome

3) Date/Months generation

4) Displaying pyramid format output

5) Identifying the duplicate letters from a string

6) Finding missing numbers in Identity Column

7) Generating any range of numbers

:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

I've used it for "cleaning" data... you know... remove all numbers, remove everything but the numbers, remove special characters, remove adjacent multiple spaces (although there are better ways to do that).

The "Tally" table is just my name for a "Numbers" or "Auxilary Numbers" or "Helper" table. Do an internet search for those other terms and I'm sure you'll come up with more uses. I've used it for some of the darnest things, but I can't remember what those oddities were...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 9

Jeff Moden (10/7/2008)
I've used it for "cleaning" data... you know... remove all numbers, remove everything but the numbers, remove special characters, remove adjacent multiple spaces (although there are better ways to do that)....

Jeff you are not allowed to post here, you can just check the possibilities what can do others with your Tally Table and then collect them and writing another Article with all things that we can do with that table!

:hehe::hehe::hehe:

Little joke here ...plz make an Article collecting every sample with tally table! We are here to post the scripts what we have done so you just generalize them if it possible!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

Dugi (10/7/2008)
Jeff Moden (10/7/2008)
I've used it for "cleaning" data... you know... remove all numbers, remove everything but the numbers, remove special characters, remove adjacent multiple spaces (although there are better ways to do that)....
Jeff you are not allowed to post here, you can just check the possibilities what can do others with your Tally Table and then collect them and writing another Article with all things that we can do with that table!
:hehe::hehe::hehe:
Little joke here ...plz make an Article collecting every sample with tally table! We are here to post the scripts what we have done so you just generalize them if it possible!

Heh... now that's funny... me not posting on a thread with "Tally Table" in the title. Ok... I get it, Dugi. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

An article would be fun, I've already seen several uses I haven't tried yet.

Karthik, you were first to suggest it (within this thread anyway). You going to write us an article detailing the many uses of Tally tables?

Or are we waiting for Porkslinger 😛 to give us another followup article?

Perhaps we should have a race and see whose article gets published first!

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Dugi SSCoach Points: 17998 More actions · Answer 12

jcrawf02 (10/7/2008)
An article would be fun, I've already seen several uses I haven't tried yet.
Karthik, you were first to suggest it (within this thread anyway). You going to write us an article detailing the many uses of Tally tables?
Or are we waiting for Porkslinger 😛 to give us another followup article?
Perhaps we should have a race and see whose article gets published first!

heyyy heyy I suggest for Article not Karthik ok! - :w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

jcrawf02 (10/7/2008)
Or are we waiting for Porkslinger 😛 to give us another followup article?

Heh... what an idea... maybe I'll title it "Porkslinger: Have Chops, Will Travel." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 14

Another use.

Generating Universal Product Code numbers.

UPC-12 is PPPPPP-NNNNN-C (hyphens added for clarity)

P: 6 digit company prefix assigned by EAN Council

N: 5 digit sequential number

C: check digit, based on modulo-10 calculation.

The tally table allows inserting 100,000 - 1 numbers (00000 to 99999) in the UPC_Numbers table in a single INSERT statement, rather than the RBAR way of looping one insert at a time and incrementing aocunter each time.

Performance gain is HUGE. Generating two UPC sets using RBAR took 38 minutes. This went down to 45 seconds using Tally table.

Or any other numbering sequence based on consecutive numbers.

RBAR inserts are poison in large quantities.