Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
latingntlman
Yak Posting Veteran
96 Posts |
 Posted - 2009-01-23 : 17:24:17
|
| What kind of storage requirements are required when creating a physical table vs. creating a temp table since I know the temp table only exists while the session is open. So for optimal disk space/performance which is better?thx,John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-01-23 : 17:50:03
|
| Also consider a table variable or a common table expression in your evaluation.Mike"oh, that monkey is going to pay" |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-01-23 : 19:11:34
|
| I've given up on table variables. We were seeing significant performance degradation in production due to the use of table variables. Switching to a temp table and adding the appropriate index solved our issue. I even opened a case with Microsoft on it. They said it was due to no statistics on table variables, which I didn't think would be an issue since we weren't talking that many rows. I believe it was a few hundred rows. If I recall correctly, we saw an 80% performance boost when we switched to a temp table with an index instead of the table variable.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-01-23 : 19:19:13
|
| Interesting? Completely opposite of what common sense would dictate.Mike"oh, that monkey is going to pay" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-25 : 22:25:19
|
| Table variables are Memory hog and it is worst when you have to deal with lots of rows. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-01-25 : 22:34:44
|
| table variables tend to suffer due to parallelism compared with temp tables and I wouldn't use them for anything of any size - they get written to tempdb anyway when they get too big.As to getting a bad query plan you often have to change the query accessing them - maybe using a derived table rather than a join.I often prefer temp tables - if nothing else because you can see them in tempdb and see how far long running sp's have got and get some indication of their size.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2009-01-26 : 16:27:41
|
| I think the general consensus is temp tables is the way to go. Thanks everyone for their valuable input.John |
|
|
|
|
|
|
Temp Tables vs. Physical Tables