How do I know when to use temp tables/table variables?

Question

Post reply

How do I know when to use temp tables/table variables?

Viewing 10 posts - 31 through 39 (of 39 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Jeff Moden (12/14/2010)I'm not sure I'd call them dangerous (by themselves, they won't cause any damage, I think) but they sure are false. Heh... it's proof positive that the internet is STILL a well paved on-ramp to a dirt road. 😉

Once there was a junior developer who thought he couldn't use constraints on a table variable so used a temp table instead, but since he didn't want the content to be rolled back if the transaction failed he copied the contents of the temp table into a table variable every time it was modified (the unique constraint some columns of the table was enforced by doing everything in a temp table before putting it into the table variable which would be used for logging). Resulting code comprehensibility was not good. Neither was resulting performance. I tried to dissuade him by suggesting that a unique constraint on the table variable would do the job, but he KNEW you couldn't do that with a table variable because he had read in on the internet, so I had to resort to kippered herrings (much more effective than pork chops, btw). So from his point of view the myth was indeed dangerous. 😀

Tom

WayneS SSC Guru Points: 95405 More actions · Answer 2

Tom.Thomson (12/15/2010)

Jeff Moden (12/14/2010)I'm not sure I'd call them dangerous (by themselves, they won't cause any damage, I think) but they sure are false. Heh... it's proof positive that the internet is STILL a well paved on-ramp to a dirt road. 😉

Once there was a junior developer who thought he couldn't use constraints on a table variable so used a temp table instead, but since he didn't want the content to be rolled back if the transaction failed he copied the contents of the temp table into a table variable every time it was modified (the unique constraint some columns of the table was enforced by doing everything in a temp table before putting it into the table variable which would be used for logging). Resulting code comprehensibility was not good. Neither was resulting performance. I tried to dissuade him by suggesting that a unique constraint on the table variable would do the job, but he KNEW you couldn't do that with a table variable because he had read in on the internet, so I had to resort to kippered herrings (much more effective than pork chops, btw). So from his point of view the myth was indeed dangerous. 😀

[Sigh]

So many people need to see my presentation... maybe at SQL Rally??? I'm counting on you guys to vote for it next month!

[/Sigh]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

No one can tell you when to use table variables and when to use temp tables. Each you need to decide which is performing well...

I would suggest you go thorugh the difference between Table Variables & Temp tables, that will help you to decide when to use temp table & table variables.

Abhijit - http://abhijitmore.wordpress.com

TomThomson SSC Guru Points: 104773 More actions · Answer 4

WayneS (12/15/2010)

[Sigh]

So many people need to see my presentation... maybe at SQL Rally??? I'm counting on you guys to vote for it next month!

[/Sigh]

Wayne, I wish I could get there, but I'm on the wrong continent, in a place where air service is restricted to very few destinations (none of them in the USA), and I don't have the money to spend on the trip anyway.

Tom

WayneS SSC Guru Points: 95405 More actions · Answer 5

Tom.Thomson (12/15/2010)

WayneS (12/15/2010)

[Sigh]

So many people need to see my presentation... maybe at SQL Rally??? I'm counting on you guys to vote for it next month!

[/Sigh]

Wayne, I wish I could get there, but I'm on the wrong continent, in a place where air service is restricted to very few destinations (none of them in the USA), and I don't have the money to spend on the trip anyway.

We're planning to reschedule it for the LV UG in January - they meet at 6:30PM PST - it will be done via LiveMeeting - is that something that you'll be able to watch remotely?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

Tom.Thomson (12/15/2010)

Jeff Moden (12/14/2010)I'm not sure I'd call them dangerous (by themselves, they won't cause any damage, I think) but they sure are false. Heh... it's proof positive that the internet is STILL a well paved on-ramp to a dirt road. 😉

Once there was a junior developer who thought he couldn't use constraints on a table variable so used a temp table instead, but since he didn't want the content to be rolled back if the transaction failed he copied the contents of the temp table into a table variable every time it was modified (the unique constraint some columns of the table was enforced by doing everything in a temp table before putting it into the table variable which would be used for logging). Resulting code comprehensibility was not good. Neither was resulting performance. I tried to dissuade him by suggesting that a unique constraint on the table variable would do the job, but he KNEW you couldn't do that with a table variable because he had read in on the internet, so I had to resort to kippered herrings (much more effective than pork chops, btw). So from his point of view the myth was indeed dangerous. 😀

Heh... kippered herrings. I've heard that if you can keep them going head first, they a whole lot more accurate than pork chops with a wobble. 😛 I think I've learned how to use their pectoral fins as hooks so that I can launch them from a Wrist Rocket sling shot. Still practicing...

Now that you've pointed out how badly the myths of Temp Tables and Table Variables can be manifested in the hands of a know-it-all, I absolutely agree... it's "dangerous code".

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

WayneS (12/15/2010)

[Sigh]

So many people need to see my presentation... maybe at SQL Rally??? I'm counting on you guys to vote for it next month!

[/Sigh]

I absolutely agree with that. Lot's of good info in that presentation. Both Roy Ernest and I can vouch for that.

--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)
Intro to Tally Tables and Functions

TomThomson SSC Guru Points: 104773 More actions · Answer 8

WayneS (12/15/2010)

We're planning to reschedule it for the LV UG in January - they meet at 6:30PM PST - it will be done via LiveMeeting - is that something that you'll be able to watch remotely?

If I know the date in advance I can try - that's about 2:30am here, so it will depend on whether I had to be up and bouncing early both the previous day and that day. Assuming I can get LiveMeeting to work on this laptop (usually do meetings using Skype video conferencing, so don't know about LiveMeeting).

edit: preliminary looks at LiveMeeting are unpromising - I don't think there's an office 2003 standard version of LiveMeeting (or if there is, I can't find it) so I guess it was only in Office 2003 professional, and I don't have that (and it's no longer for sale). I definitely don't want to upgrade to Office 2007 or Office 2010 until I upgrade my whole platform, so it looks as if I won't be able to watch it.

Tom

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Tom.Thomson (12/16/2010)

WayneS (12/15/2010)

We're planning to reschedule it for the LV UG in January - they meet at 6:30PM PST - it will be done via LiveMeeting - is that something that you'll be able to watch remotely?

If I know the date in advance I can try - that's about 2:30am here, so it will depend on whether I had to be up and bouncing early both the previous day and that day. Assuming I can get LiveMeeting to work on this laptop (usually do meetings using Skype video conferencing, so don't know about LiveMeeting).

edit: preliminary looks at LiveMeeting are unpromising - I don't think there's an office 2003 standard version of LiveMeeting (or if there is, I can't find it) so I guess it was only in Office 2003 professional, and I don't have that (and it's no longer for sale). I definitely don't want to upgrade to Office 2007 or Office 2010 until I upgrade my whole platform, so it looks as if I won't be able to watch it.

You can buy it separate, and there's a 30 day trial available, in case you were still curious.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95405 More actions · Answer 10

I didn't have to pay anything for it... when I clicked on a LiveMeeting link, it allowed me to just download it.

Contact Jason as to the date of the meeting. I believe that it will be on Jan 13,2011.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2