Adding a column to a table

Question

Post reply

Adding a column to a table

Mark Harley

SSCrazy

Points: 2259
More actions
November 5, 2007 at 5:48 am

#199626

I have a temp table (in a stored procedure) of grouped data that I need to add a column to. The table consists of the sums of various fields, and the column that I want to add is the sum of one of those sum-columns (so every row in the new column would have the same value -- the sum of the sum-column). Does this make any sense? I've tried storing this value in another temp table and joining the two, but I haven't had any success with that yet. Any suggestions?

Viewing 12 posts - 1 through 11 (of 11 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Mark

My suggestion would be not to bother with an extra column, but instead to store that single extra value in a variable.

John

Steve Jones - SSC Editor SSC Guru Points: 734794 More actions · Answer 2

Can you explain what you are trying to do? Any reason you can't do the math inline with your other queries/operations?

Mark Harley SSCrazy Points: 2259 More actions · Answer 3

I wasn't sure if it was possible to do the math inline since it relies on a sum of one column to produce the value for another column. Was I mistaken? I did get it to work using temp tables, but this might be a bit of a memory-hog.

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 4

Have you tried adding a calculated column to your temp table?

Alter Table #MyTemp

Add MyCalcCol AS (Col1 * Col2)

Or something like that. Where the "datatype" isn't actually a datatype but the formula you're using to determine your total value.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 5

Or, if you have to sum the entirity of Col1 first, then create a second Temp table with a column that is the sum of the entirity of Col1 and then ( @shudder... I can't believe I'm suggesting this...) do a CROSS JOIN to your second table from the first.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 6

John Mitchell (11/5/2007)
Mark
My suggestion would be not to bother with an extra column, but instead to store that single extra value in a variable.
John

That would be my suggestion, as well.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Brandie Tarvin (11/9/2007)
Or, if you have to sum the entirity of Col1 first, then create a second Temp table with a column that is the sum of the entirity of Col1 and then ( @shudder... I can't believe I'm suggesting this...) do a CROSS JOIN to your second table from the first.

well, then again... a CROSS JOIN again a 1 row, 1 column table isn't going to kill you..

Still - calculating it up front and passing it in as a scalar variable would still be faster.

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

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 8

I agree that the variable is better and faster, but I wanted to put an alternate suggestion out there just in case.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Mark Harley SSCrazy Points: 2259 More actions · Answer 9

Would it really be faster to go through the entire table to calculate the sum of the column for the variable? I suppose that is what I'm doing with the temp tables, anyway (the temp table only includes the one field, so no extra calculations are being done). Would this be just as fast as storing it in a variable? Is there much difference in the amount of memory used?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Brandie Tarvin (11/9/2007)
I agree that the variable is better and faster, but I wanted to put an alternate suggestion out there just in case.

Brandie - (I was actually agreeing with you ;), so stop conceding :D)

Mark - if your tables are indexed well - coming up with a sum shouldn't be a big factor one way or the other, so calculating it on the fly, put it into a variable and pass the variable into the other calcs should do pretty well. It will be more work if you store the data, since you'd have to calculate it and then go back and store it (and you would have to do that each time any value changed, so storing it buys you "nothing").

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

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 11

Mark,

I suggest you try it both ways and run Profiler on both runs. That will tell you right there which is the faster and better one for your specific needs.

Matt - I agree, I agree, I agree... I'm in such an agreeable mood today... (...and agree and agree and agree) @=)

Good thing my boss is OOO today. Otherwise I might "agree" to do tasks that I'd regret next week. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.