April 30, 2008 at 6:33 am
Jeff,
As i am going to suggest to use Tally table in Prod environment,I need to prepare myself to explain about Tally table.
I know there are around 1000's of use while using Tally Table.
I also went through the following URL.
http://www.sqlservercentral.com/Forums/Topic469152-8-1.aspx#bm469793
http://www.sqlservercentral.com/Forums/Topic483382-338-2.aspx
Really you gave very good reply.
My polite request is please add your valuable comments for the below questions.
1) what is Tally table ?
Simply, it is a replacement of Loop. If you want to add some more points it would be appreciable.
2) will it affect prod DB in anyway ?
I know the answer is 'No', even though just i wanted to know.
3) Why we go for Tally table ?
Simply, To apply SET BASED Logic. If you want to add some more points it would be appreciable.
4) How much size will it occupy in the DB ?
In my PC, i saw Reserved = 144 KB,Data = 134 KB ,UnUsed = 10KB
5) Incase if i need more than 11000 rows or my query requires more than 11000 so what is the maximum value that we can kept Because i don't want to touch the table after moving into production ?
Honestly expecting your vaulable answers!
karthik
May 2, 2008 at 12:23 am
Answers are welcome !
karthik
May 2, 2008 at 6:05 am
In addition to the references you have cited you might also give a look at these two references:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home
At one time I referred to my version as an "iterator" table -- definitely a useful utility table.
May 2, 2008 at 7:26 am
Well - My name's not Jeff, so I'm not sure the question is directed at me, but I'll take a crack at it.
1) what is Tally table ?
Simply, it is a replacement of Loop. If you want to add some more points it would be appreciable.
Actually - no it's not a replacement of a loop. It's a tool/trick sometimes used in set-based code, the set-based code being used to avoid any procedural construct such as loops.
One's a table, possibly extending to some programming techniques involving a table, and the other is a procedural program construct, so they can't be direct replacement for each other.
2) will it affect prod DB in anyway ?
I know the answer is 'No', even though just i wanted to know.
The cost of storing a tally table should in most cases be minor. The benefits from using a tally table will usually NOT be minor, so if anything - there's a cost but it's well worth the benefits.
3) Why we go for Tally table ?
Simply, To apply SET BASED Logic. If you want to add some more points it would be appreciable.
Correct use of the tally table can result in processes substantially more efficient than their procedural counterparts. Its most common uses involve set-based constructs (although not all uses are strictly set-based).
4) How much size will it occupy in the DB ?
In my PC, i saw Reserved = 144 KB,Data = 134 KB ,UnUsed = 10KB
5) Incase if i need more than 11000 rows or my query requires more than 11000 so what is the maximum value that we can kept Because i don't want to touch the table after moving into production ?
It's as big as you're willing for it to be. Depending on how big you wish it to be - you can define the sequence column in this table to be BIGINT, in which case you can hold all of the integers covered within the BIGINT range , which according to BOL is :
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 3, 2008 at 1:17 am
Nicely done, Matt! I guess my only exception to what you said is that I use it to "replace loops" all the time. Although.... I'll agree that it's not the same as a loop so, technically, it's not a replacement... it's a set based substitution.
Karthik, if you want to know how it works, I have an article coming out on May 7th (just 4 more days... :hehe:) called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
I generally recommend a size of 11,000 in SQL Server 2000 and the knowledge of how to build a nasty fast CTE in place of a Tally table in SQL Server 2005. Trust me, the CTE has nothing to do with recurrsion to be as fast as it is. 😉 The article explains all of this.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2008 at 9:21 pm
Jeff Moden (5/3/2008)
Nicely done, Matt! I guess my only exception to what you said is that I use it to "replace loops" all the time. Although.... I'll agree that it's not the same as a loop so, technically, it's not a replacement... it's a set based substitution.Karthik, if you want to know how it works, I have an article coming out on May 7th (just 4 more days... :hehe:) called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
I generally recommend a size of 11,000 in SQL Server 2000 and the knowledge of how to build a nasty fast CTE in place of a Tally table in SQL Server 2005. Trust me, the CTE has nothing to do with recurrsion to be as fast as it is. 😉 The article explains all of this.
Nice Mastah Jeff!!! surely an article worth reading 😀
"-=Still Learning=-"
Lester Policarpio
May 4, 2008 at 9:29 pm
Gosh, I sure hope so, Lester. Thank you for the confidence. I look at some of the articles I've written and say to myself, "Everyone probably knows that anyway... hope I don't insult anyone". The upcoming article on the how the Tally/Numbers table works seems very simple... but I know a lot of folks that use it without knowing how it actually works. I sure do hope I don't insult anyone with the simplicity I've built into the article.
Thank you again, for the confidence, Lester.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2008 at 9:45 pm
Jeff Moden (5/4/2008)
quote]
I think I'm a living proof for this 😀 Just keep up the good work looking forward for more interesting and mind boosting articles 🙂
"-=Still Learning=-"
Lester Policarpio
May 5, 2008 at 1:15 am
Karthik, if you want to know how it works, I have an article coming out on May 7th (just 4 more days... :hehe called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
I am eagerly waiting for your Article.:)
karthik
May 5, 2008 at 12:40 pm
"Hey, Mr. Tally Man, tally my big table. Daylight's gone and I wanna go home..."
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 5, 2008 at 1:00 pm
Another excellent article on tally/numbers tables is at:
http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
by the lovely Robyn Page and the hillarious Phil Factor (I hope I didn't get that backwards 🙂 ).
The only thing I can really add to the subject of numbers tables is that I use two. The most common goes from 0 to 10,000, and is called Numbers. I also keep a table called BigNumbers that goes from 0 to 100-million. That one doesn't get used much, but every now and again it comes in really handy.
My preference, since I administer multiple databases on multiple servers, is to keep a "Common" database on each server. I keep Numbers and BigNumbers in the common database, alias them in the other databases, and thus don't have to use quite as much disk space, and keep them out of my backups and such.
(There are several things I keep in my Common database and then use from multiple other databases on the same server. A few functions, a table of Zip codes, stuff like that.)
(Of course, aliasing doesn't work in SQL 2000, but 3-part names do.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2008 at 1:30 pm
"Everyone probably knows that anyway... hope I don't insult anyone".
Jeff, as long as I am around, you needn't worry that everyone already knows anything.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 5, 2008 at 11:32 pm
GSquared (5/5/2008)
Another excellent article on tally/numbers tables is at:http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
by the lovely Robyn Page and the hillarious Phil Factor (I hope I didn't get that backwards 🙂 ).
The only thing I can really add to the subject of numbers tables is that I use two. The most common goes from 0 to 10,000, and is called Numbers. I also keep a table called BigNumbers that goes from 0 to 100-million. That one doesn't get used much, but every now and again it comes in really handy.
My preference, since I administer multiple databases on multiple servers, is to keep a "Common" database on each server. I keep Numbers and BigNumbers in the common database, alias them in the other databases, and thus don't have to use quite as much disk space, and keep them out of my backups and such.
(There are several things I keep in my Common database and then use from multiple other databases on the same server. A few functions, a table of Zip codes, stuff like that.)
(Of course, aliasing doesn't work in SQL 2000, but 3-part names do.)
Good plan on the "Common" database... allows you to do a lot of different things without having to worry about backups on easily replaceable data. Also allows the database to be set to the Simple recovery mode so when you import large tables like a ZipCode or any of a myriad of cross reference tables, you don't have to worry about the transaction log much.
You guys probably already know this, but you can cheat a bit in that area... doesn't work for functions but you can make "pass-through" or "synonym" views for the tables so that you don't need to use 3 part naming conventions if you don't want to. A big advantage there, is with "picky" clients who want a "tool" database to be named something differently than what you expected. With the synonym views, all you have to do is change the name of the database and you don't need to touch any of your code if it uses the 2 part naming convention. That also covers the eventuality of a client that want's everything is a single database.
For example, if your code says something like...
SELECT yada-yada
FROM dbo.ZipCode
WHERE someconditionsaremet
... the ZipCode table can live in any database if you have a view in the current database that says...
SELECT * FROM somedbname.dbo.ZipCode
No matter where the table lives, you can still get away with 2 part naming so you don't need to change your code if the customer has a different idea as to where such tool tables should live. And, if ZipCode is in the current database, you simply don't create the view.
Of course, you have to be a bit careful... can't do Declared Referential Integrity using Foreign Keys across databases. But, for most tool tables like a "Tally" table, DRI isn't applicable in most cases.
I do have a question for anyone that cares to answer... What do folks use things like a 100 million row Tally table for? I can see some uses in 2k5 like splitting monster parameters that are larger than 8k, but what do folks use such large Tally tables for in 2k?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2008 at 7:07 am
I do have a question for anyone that cares to answer... What do folks use things like a 100 million row Tally table for? I can see some uses in 2k5 like splitting monster parameters that are larger than 8k, but what do folks use such large Tally tables for in 2k?
Dateadd lists of datetimes. For example, if you need a second-by-second list of all times in a month, or a quarter. (10,000 seconds is a little less than 3 hours. 100-million seconds is just over 3 months.) Millisecond lists for a full day (10,000 milliseconds = 10 seconds. 100-million is a little over 2 days.) Creation of test data tables (select whatever from dbo.BigNumbers), to get millions of rows of dates, text, numbers, whatever, with one select statement.
It's nothing that gets run all that often, but it does come up every now and again, enough so that having the table has been worth the minor cost in disk space (12.5 Meg) is worth more than the cost of cross-joining Numbers to itself. The value is minor, but the cost is even more negligible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 7:16 am
On the subject of a Common or Tools database (good name for it, "Tools", but I'll stick with "Common" from habit), the main thing to remember is that cross-database queries, even on the same server, do suffer about a 5%-6% speed penalty. No big deal in most cases, but if that's enough to matter (overloaded server or something like that), it needs to be known.
The advantages, in terms of simple logging, production database backups, and code-library like functionality, are worth it to me.
Currently, my Common database has tables for Dates, Numbers (and BigNumbers), and FirstNames (equivalence table for things like Robert = Rob = Bob = Robby = Bobby = Bert), a function for string parsing, and a proc for returning or logging error data in Catch blocks (got tired of writing the same code over and over and over again in hundreds of procs in multiple databases).
Should have more than that, but doesn't currently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply