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 |
|
collie
Constraint Violating Yak Guru
400 Posts |
 Posted - 2012-05-29 : 14:48:00
|
| Hi,What are the important differences btw the two and when to use one over the other? Thanks |
|
|
Gigabyte
Starting Member
30 Posts |
Posted - 2012-05-29 : 14:53:45
|
| Temp variable or Temp tables are instance specific (SPID) which you see in SSMS.Difference is that variable would hold just the data and temp table would hold table data.This would save lot of space if you do not want to save the tables while you are using cursors or sprocs. Please add if I have missed anything.SATISH GEDDAMURI |
 |
|
|
collie
Constraint Violating Yak Guru
400 Posts |
Posted - 2012-05-29 : 15:18:14
|
| Thanks. I don't understand what you mean by 'variable would hold just the data and temp table would hold table data'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-29 : 15:26:38
|
quote:
Originally posted by Gigabyte
Temp variable or Temp tables are instance specific (SPID) which you see in SSMS.Difference is that variable would hold just the data and temp table would hold table data.This would save lot of space if you do not want to save the tables while you are using cursors or sprocs. Please add if I have missed anything.SATISH GEDDAMURI
Not correcttable variable and temp table are both storing tabular data itselftable variable or @ tables are just like other variables and it doesnt have any statistics associated with it and neither can you create indexes on them. scope of table variable is same as for normal variable ie only within batch in which you declare it.table variables won’t cause a statement recompilation, they don’t participate in transactions temp table or # tables on the other hand are created in tempdb and the scope is within connection in which you created it. You can create indexes within them and they also store statistics information. temporary tables will cause statement to recompile and they can take part in transactions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-29 : 15:50:44
|
| Partially true.Temp tables and Table Variables are both held in memory until a certain threshold is met; and then they are persisted to disk in tempdb.Table variables can have a PK. But, it is true there are no statistics maintained and you cannot create other indexes. |
|
|
|
|
|
|
table variable vs temp table