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 |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2008-07-03 : 12:06:27
|
| I have a stored procedure that needs to use a "temp" table. Should I use a temp table or table variable. Several users may run the same stored procedure at the same time (need their own instance of the temp table for the duration). Thank you. |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2008-07-03 : 12:17:06
|
| see this link to know more about Temp Table vs Table Variablehttp://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-03 : 13:33:52
|
| If its more than one or two hundred records, use a temp table.Also, consider migrating some of this logic to a Common Table Expression instead.e4 d5 xd5 Nf6 |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2008-07-03 : 13:45:23
|
| Thank you. Just to confirm: A temp table will have its own instance foe each user, correct? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-03 : 14:49:54
|
| Local temp table (one #): yes.Global temp table (two ##): no.e4 d5 xd5 Nf6 |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-07-03 : 14:56:30
|
| One thing that might help also is depending on how many rows it needs to hold and whatever the SP is doing with the table, an index could really speed things up. Indexes can't be created on table variables but can be created on temp tables. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-03 : 15:24:00
|
indexes can be created on table variables, but only one: the PK.declare @t table (col1 int not null primary key, col2 int)
elsasoft.org |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2008-07-03 : 15:28:25
|
| Got it. Thank you guys. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-04 : 04:10:30
|
| And one more thing...If your stored procedure needs to call another stored procedure that uses your built dataset you can't use a table variable. It won't be accessible to your child stored proc. You'd have to use a temp table in that case.-------------Charlie |
|
|
|
|
|
|
[Resolved] Temp Table vs Table Variable