SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Twit List
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 88

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/05/2012 :  18:42:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
tally table ++ ++ ++

so useful, so very very useful,

also calendar table

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/05/2012 :  20:14:25  Show Profile  Reply with Quote
I create Tally tables in my databases where I am allowed to. In vendor databases or when I am visiting someone else's databases, I use master..spt_values. Up until recently, I used to painfully create a temp table and fill it with numbers.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/05/2012 :  22:55:22  Show Profile  Reply with Quote
quote:
Originally posted by Transact Charlie

tally table ++ ++ ++

so useful, so very very useful,

....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION




Wont be useful anymore in the near future with Denali's SEQUENCE object.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/06/2012 :  04:41:36  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
A sequence isn't going to replace tally tables.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/06/2012 :  05:05:37  Show Profile  Reply with Quote
What I meant was the way the tally tables are going to be populated using sequence object.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/06/2012 :  06:28:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What is the benefit of that method?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/06/2012 :  06:38:48  Show Profile  Reply with Quote
No need to write complex cross join queries when you need to create sequence of range values.Say I need sequence of only even numbersin a tally table.say 2,4,6,8,....100000000. Just set the lower and upper limit in the sequence and you are done.


Create sequence MySeq

as int

Start with 2

increment by 2

maxvalue 100000000


After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 01/06/2012 :  09:39:51  Show Profile  Reply with Quote
Slightly off tack I know but this just has to go here

On one of our systems that allows people to buy items, they can order a minimum of 5 (nothing unusual there). For a brief period we allowed them to purchase a single item.

There is no variation in these items they are all identical. When people fill in the form, when they select how many they want, its on a drop down, with just numeric values 5-40 (don't ask, suffice to say I didn't design it and I can't change it now) with the temporary value of one.

I happened to notice that one person had purchased one item 5 times, this took him over half an hour and he will have paid 5 seperate times due to the way the system works


-----------

What color do you want that database?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/06/2012 :  10:31:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Sachin.Nand

No need to write complex cross join queries when you need to create sequence of range values.Say I need sequence of only even numbersin a tally table.say 2,4,6,8,....100000000. Just set the lower and upper limit in the sequence and you are done.


Create sequence MySeq

as int

Start with 2

increment by 2

maxvalue 100000000


And how are you going to populate the tally table?
What is the benefit of using the sequence object in favor of

1. CROSS JOIN and ROW_NUMBER()
2. GO xxx




N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/08/2012 :  08:02:47  Show Profile  Reply with Quote

quote:
Originally posted by SwePesoAnd how are you going to populate the tally table?




INSERT TalyTable (num) VALUES (Next Value for Sequence)
Go N times

quote:
Originally posted by SwePeso
What is the benefit of using the sequence object in favor of

1. CROSS JOIN and ROW_NUMBER()
2. GO xxx




N 56°04'39.26"
E 12°55'05.63"




I think I have already answered that in my previous reply.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/08/2012 :  08:38:16  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by Sachin.Nand


quote:
Originally posted by SwePesoAnd how are you going to populate the tally table?




INSERT TalyTable (num) VALUES (Next Value for Sequence)
Go N times


Wow, seriously? How is that any different from declaring the tally table to have an identity column (which doesn't have to start at zero and doesn't have to increment by 1) and inserting default values with a GO N?

I though you were at least going to use the ability of a sequence to get a set of values, not one at a time a few thousand times.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/08/2012 :  13:14:41  Show Profile  Reply with Quote
quote:
Originally posted by GilaMonster

Wow, seriously? How is that any different from declaring the tally table to have an identity column (which doesn't have to start at zero and doesn't have to increment by 1) and inserting default values with a GO N?

Gail Shaw
SQL Server MVP



Thank you very much for letting me know that an "identity column has dosent have to start at zero and doesn't have to increment by 1" or else I would had been in oblivion darkness.

quote:

I though you were at least going to use the ability of a sequence to get a set of values, not one at a time a few thousand times.



Not sure what you meant by above ?

As I am writing this I am feeling pretty sure that Sequence is indeed a substiution for tally tables.

How is a tally table going to solve the scenarios below ?

Need to have a series of number being resued all over again after a specific value is reached.

Need to reserve the value so that it cannot be resused for other concurrent operations.

Need to assign the consecutive set of values for multiple tables at same time.

Not to mention that Sequence performs way better than a tally table.The most disappointing part of Sequence for me has been the inability to add increments in decimal values.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/08/2012 :  13:55:54  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
But none of those are what tally tables are used for.

Tally tables are used when a fixed sequence of numbers is needed (eg, string splits, for generating sequences of dates for things like timesheets/schedules where some dates are missing but must be reported), a sequence, usually starting at 1 and ending at some large value, that will be needed in that form repeatedly. They're not used to 'reserve values' or 'assign consecutive values for multiple tables' or anything like that.

A tally table is one that you populate once, and once populated with however many numbers are needed, is never inserted or updated again. It's a static set of numbers (and sometimes a dates). That's why spt_values (which has among other things some rows with the consecutive values 1..255 in them) can be used as a substitute for a tally table if there isn't one in the system

I suspect you're thinking about an 'autonumber' table, where procedures select the value and then increment it, using that value they got as a substitute identity. Those can be replaced by sequences, but those aren't tally/numbers tables.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/08/2012 :  14:16:04  Show Profile  Reply with Quote
No actually I do mean by tally table which are pre loaded and static.

Let me tell you one scenario where I had a tally table with a sequence of numbers.I had to populate another table with consecutive numbers from that tally table and once if reached the max value from the tally table I had to use the numbers in the tally table from the start.I had to go through so much pain to track that.

But with a sequence could had easily achieved that.I would had simply used the CYCLE part of the sequence.Something like this

Create Sequence MySequence
as int
start with 1
increment by 1
minvalue 1
maxvalue 1000
Cycle;

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/08/2012 :  14:30:28  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Fine, then use a sequence for that (though I suspect you simplified the problem, because I can think of a few ways to use a tally table for what you describe). But saying that a sequence replaces a tally table is not true, they're completely different objects, sequences are there to generate numbers on the fly, as you need, tally tables are there to provide a fixed sequence of numbers for a variety of problems. Two different animals.



--
Gail Shaw
SQL Server MVP
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/08/2012 :  14:54:02  Show Profile  Reply with Quote
quote:
Originally posted by GilaMonster

Fine, then use a sequence for that (though I suspect you simplified the problem, because I can think of a few ways to use a tally table for what you describe)...
--
Gail Shaw
SQL Server MVP



I Cant as SQL server 2012 is still not out .Still stuck to that old logic of If's and But's..

quote:
.... But saying that a sequence replaces a tally table is not true, they're completely different objects, sequences are there to generate numbers on the fly, as you need, tally tables are there to provide a fixed sequence of numbers for a variety of problems. Two different animals.



Actually I came to the above conclusion as I did face some issues where tally tables were of no use except for providing a series of sequence numbers with no flexibilty while sequence could had easily solved all the problems which I had quoted earlier.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/08/2012 :  15:11:22  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
That's because none of the problems you listed above are ones that a tally table solves. Hammer, crowbar.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2145 Posts

Posted - 01/09/2012 :  13:56:11  Show Profile  Reply with Quote
quote:
Originally posted by GilaMonster

That's because none of the problems you listed above are ones that a tally table solves. Hammer, crowbar.

--
Gail Shaw
SQL Server MVP



Hammer. Works. Every. time.
Provided it is big enough!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/09/2012 :  14:48:27  Show Profile  Reply with Quote
If everything looks like a nail



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/09/2012 :  15:07:16  Show Profile  Reply with Quote
But the syntax of this hammer looks awfully nice, doesn't it? Almost conversational.
Create Sequence MySequence
as int
start with 1
increment by 1
minvalue 1
maxvalue 1000
Cycle;
It is so unlike some of the other SQL thingies with godawaful abbreviations - sp_dbcmptlevel, for example. How in the world did they come up with spelling? sp_db_compat_level or sp_db_compatibility_level or sp_db_c_level or sp_db_compatibility or a zillion other more readable abbreviations? Nah... they had to use sp_dbcmptlevel!!
Go to Top of Page
Page: of 88 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000