The Tally Table

Question

The Tally Table

Viewing 15 posts - 31 through 45 (of 87 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

g.britton (8/14/2015)
I just wish it wasn't called a "Tally" table. I don't tally things, I count them. fwiw I usually call my "tally" table "N" to reflect it's relation ship to the set of natural numbers, usually represented by a double-struck N (now let's not argue if it should contain 0 (CS) or not (mathematics)).

Jeff Moden explains why he calls it a "tally table" in the article below. "Numbers table" is common. Itzek Ben-Gan calls it a Virtual Auxiliary Table of Numbers. I call it tally because it's simple - "virtual auxiliary table of numbers" is, by far, the coolest sounding though.

http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

g.britton (8/14/2015)
I just wish it wasn't called a "Tally" table. I don't tally things, I count them. fwiw I usually call my "tally" table "N" to reflect it's relation ship to the set of natural numbers, usually represented by a double-struck N (now let's not argue if it should contain 0 (CS) or not (mathematics)).

Celko talks about "tally tables" in his book SQL for Smarties, but he calls them "series tables".

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

g.britton SSChampion Points: 13863 More actions · Answer 3

Alan.B (8/14/2015)
g.britton (8/14/2015)
I just wish it wasn't called a "Tally" table. I don't tally things, I count them. fwiw I usually call my "tally" table "N" to reflect it's relation ship to the set of natural numbers, usually represented by a double-struck N (now let's not argue if it should contain 0 (CS) or not (mathematics)).
Jeff Moden explains why he calls it a "tally table" in the article below. "Numbers table" is common. Itzek Ben-Gan calls it a Virtual Auxiliary Table of Numbers. I call it tally because it's simple - "virtual auxiliary table of numbers" is, by far, the coolest sounding though.
http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

Yeah, Jeff's reason doesn't exactly resonate with me. "Tally table" doesn't sound cool to my ears, just weird IBG's is weirder still and for many it's not virtual. Lots of folks persist this table.

Gerald Britton, Pluralsight courses

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 4

I agree with one poster who said that seeing examples by some of you that have used them to get set based answers, or simplify complex queries, would be very useful. I have never used one.

Student of SQL and Golf, Master of Neither

g.britton SSChampion Points: 13863 More actions · Answer 5

BobAtDBS (8/14/2015)
I agree with one poster who said that seeing examples by some of you that have used them to get set based answers, or simplify complex queries, would be very useful. I have never used one.

See Jeff's excellent article: http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

Gerald Britton, Pluralsight courses

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Luis Cazares (8/14/2015)
Lynn Pettis (8/14/2015)
g.britton (8/14/2015)
What's the purpose of doing a cross join between the master.dbo.syscolumns table, and then not using it? Sorry, inquiring minds want to know.
to get enough numbers to handle large applications
Shouldn't use dbo.syscolumns. That system table is one of those deprecated and may be removed from a future version of SQL Server. Should use sys.columns or sys.all_columns instead. Or you could use this in SQL Server 2008 and newer:
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
eTally(n1) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rows
select n from eTally;
dbo.syscolumns is used because the code is intended for a 2000 instance.

I'm sorry, at least it isn't a 7.0 or worse a 6.0/6.5 instance.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Rod at work (8/14/2015)
Oh OK Luis, I get it now. I got hung up on trying to read what's in syscolumn. That's not the point at all, is it. The original poster is just using that table as a way of casing an iteration to occur. Ah, now I understand. Thanks! (You know, I would not have thought of that. Thanks!!)

Like Luis said, you don't care about what's in the rows. You care about the presence of rows because that's what you're using to generate your numbers.

Others have already posted the link to Jeff's article. I'd recommend starting there in learning about them.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 8

I have read Jeff's article, both in the past and an hour ago. His examples aren't things we do at the TSQL level very often. I'm wondering what other uses folks have?

Student of SQL and Golf, Master of Neither

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

g.britton (8/14/2015)
Alan.B (8/14/2015)
g.britton (8/14/2015)
I just wish it wasn't called a "Tally" table. I don't tally things, I count them. fwiw I usually call my "tally" table "N" to reflect it's relation ship to the set of natural numbers, usually represented by a double-struck N (now let's not argue if it should contain 0 (CS) or not (mathematics)).
Jeff Moden explains why he calls it a "tally table" in the article below. "Numbers table" is common. Itzek Ben-Gan calls it a Virtual Auxiliary Table of Numbers. I call it tally because it's simple - "virtual auxiliary table of numbers" is, by far, the coolest sounding though.
http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
Yeah, Jeff's reason doesn't exactly resonate with me. "Tally table" doesn't sound cool to my ears, just weird IBG's is weirder still and for many it's not virtual. Lots of folks persist this table.

One of the definitions of the term "Tally" means "to count", which is exactly what the table does.

http://www.vocabulary.com/dictionary/tally

I've lost the URL for it and haven't been able to find it but there was a fellow that worked with computers in the '60s that also called such a table a "Tally Table".

It doesn't matter to me what you call it though.

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

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

BobAtDBS (8/14/2015)
I have read Jeff's article, both in the past and an hour ago. His examples aren't things we do at the TSQL level very often. I'm wondering what other uses folks have?

Do you use Row_Number() for things in your code? Same principle but can sometimes be even more useful because it can all be done inline and the PARTITION BY makes it even more useful for all sorts of things. Itzik Ben-Gan is also pretty famous for using the differences between two Row_Number()'s for doing some pretty remarkable things such as a lightning fast method for determining overlapping date ranges.

The Tally Table is just a single physical manifestation for the principle of "pseudo-cursors", which a term coined right here on SQLServerCentral by R. Barry Young several years back.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Rod at work (8/14/2015)
Oh OK Luis, I get it now. I got hung up on trying to read what's in syscolumn. That's not the point at all, is it. The original poster is just using that table as a way of casing an iteration to occur. Ah, now I understand. Thanks! (You know, I would not have thought of that. Thanks!!)

And now you know what the definition of a "pseudo-cursor" is. 😀

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Lynn Pettis (8/14/2015)
g.britton (8/14/2015)
What's the purpose of doing a cross join between the master.dbo.syscolumns table, and then not using it? Sorry, inquiring minds want to know.
to get enough numbers to handle large applications
Shouldn't use dbo.syscolumns. That system table is one of those deprecated and may be removed from a future version of SQL Server. Should use sys.columns or sys.all_columns instead. Or you could use this in SQL Server 2008 and newer:
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
eTally(n1) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rows
select n from eTally;

Agreed that you should no longer use dbo.syscolumns. As you've seen in all my posts for the last couple of years, I do use sys.all_columns because it's one of the larger tables right out of the box. The original article was written back when a lot of people still had SQL Server 7 and 2000 and a lot of people had the relatively new 2005. I didn't actually have 2005 at the time and so couldn't actually test with it. I did know that it would work for both at the time so that made it all easy for me.

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

BobAtDBS (8/14/2015)
I have read Jeff's article, both in the past and an hour ago. His examples aren't things we do at the TSQL level very often. I'm wondering what other uses folks have?

Some examples include:

* filling in missing sequence numbers & dates

* creating sample data

* many kinds of string manipulation

* generating a series of numbers, dates, IP addresses

* Splitting a string based on a delimiter or pattern (see both links in my signature line)

* breaking a string into unigrams, bigrams, trigrams, ngrams...

* duplicating values

* pretty much anything you would use a loop for

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Alan.B (8/14/2015)
BobAtDBS (8/14/2015)
I have read Jeff's article, both in the past and an hour ago. His examples aren't things we do at the TSQL level very often. I'm wondering what other uses folks have?
Some examples include:
* filling in missing sequence numbers & dates
* creating sample data
* many kinds of string manipulation
* generating a series of numbers, dates, IP addresses
* Splitting a string based on a delimiter or pattern (see both links in my signature line)
* breaking a string into unigrams, bigrams, trigrams, ngrams...
* duplicating values
* pretty much anything you would use a loop for

Just yesterday, I used a "micro" Tally table to create "safe" temporary passwords that won't spell English swear words or even "WTF".

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 15

Jeff Moden (8/14/2015)
Alan.B (8/14/2015)
BobAtDBS (8/14/2015)
I have read Jeff's article, both in the past and an hour ago. His examples aren't things we do at the TSQL level very often. I'm wondering what other uses folks have?
Some examples include:
* filling in missing sequence numbers & dates
* creating sample data
* many kinds of string manipulation
* generating a series of numbers, dates, IP addresses
* Splitting a string based on a delimiter or pattern (see both links in my signature line)
* breaking a string into unigrams, bigrams, trigrams, ngrams...
* duplicating values
* pretty much anything you would use a loop for
Just yesterday, I used a "micro" Tally table to create "safe" temporary passwords that won't spell English swear words or even "WTF".

Very interesting. I've bever seen that before. It would be cool to see a sample of how you did that.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001