| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-30 : 08:55:52
|
| Tend to use temp tables for large datasets as there are more optimisation option and indexing capability (also handy to see how far an SP has got if you don't log everything).Table variables for small things.I tend to develop with one of the other - temp tables will be a start for long running things as they persist the data and it's easier to check what's going on.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-11-30 : 09:11:52
|
| This is a obscure question. What kind of housekeeping are you looking for? You can empty the table variable by doing a DELETE @t but to remove it completely you'd have to change scope. Is this in relation to a problem you have or just out of curiosity?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-30 : 15:38:55
|
| It's automatically dropped as soon as it goes out of scope. It's treated like a variable, so just as you can't undeclare a normal variable, you can't undeclare a table variable.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-01 : 00:52:50
|
| If you're putting enough data into a table variable that freeing up the resources is important, I suspect it really should be a temp table (for the stats and indexability)--Gail ShawSQL Server MVP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-12-01 : 01:08:17
|
Table variables has it's uses even if the optimizer always assumes a table variable only has one and one record only, doesn't have statistics and so on.The most important use is that table variables are unaffected by transactions, whereas temp tables are affected by transactions.So, if you want to keep data even after a rollback, use a table variable.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-01 : 04:44:30
|
quote:
Originally posted by tkizer
I don't use table variables in any code anymore. We had a major performance issue that was resolved by switching to a temp table. Our table variable had about 5 rows in it, and yet it (the optimizer) thought differently due to the lack of statistics.I know the recommendation is to use table variables for small data sets, however I don't think that recommendation is valid.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
There was a thread recently (think it might have been sql server central) where someone got vast improvements from a tabe variable - down from getting on for a second down to virtually nothing.It makes a big difference if you are dealing with a lot of small statments (I mean in the millions) but if you are dealing with batch systems which should be built to work on more data at once then the preformance difference shouldn't really impact things - you'll probably get more benefit from using whatevers easier to allow you to do more testing. Split things up into smaller statments unless a larger one gives a lot of benefit - it'll be easier to optimise and see where problems are.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-01 : 10:49:50
|
| Sounds a bit like an issue I had a while back.Would take about an hour to join to a temp table but if I looped through the table using a variable it would take seconds for each one - a couple of minutes in total.This sort of thing used to happen frequently - fortunately the optimiser tends to be a bit better now but still throws up oddities.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
@Local Table variable