| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2006-05-24 : 08:45:34
|
| Where can I use Temp Table and where can I use Table Variable. If data is needed in the table for some manipulation inside the stored proc, table variable makes sense for me.Anybody can give me pointers on performance front? Also if two users access the sp at the same time, what will happen in the case temp table? coz, the first line of my sp is to drop the temp table if it exists.ThanksKarunakaran |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-24 : 08:56:23
|
from Books On Line on CREATE TABLEquote:
Temporary TablesYou can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
2 users = 2 connection = 2 session. No problem.You can use QA to try this out. Open 2 window and create temp table of same name in both window.
KH |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2006-05-24 : 09:09:59
|
quote:
Originally posted by khtan
from Books On Line on CREATE TABLEquote:
Temporary TablesYou can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
2 users = 2 connection = 2 session. No problem.You can use QA to try this out. Open 2 window and create temp table of same name in both window.
KH
Lets say #tmp is my temp table.If I use the same object name in 2 different sessions,then it should create 2 #tmp objects, so Sql Server internally will give different alias / name for these 2 objects?Karunakaran |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-24 : 09:17:11
|
Try to run this in 2 QA windowcreate table #temp (col1 int)select * from tempdb..sysobjects where name like '#temp%'
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-05-24 : 10:37:48
|
I'm waiting to see the answer for :quote:
Originally posted by karuna
Where can I use Temp Table and where can I use Table Variable.
I mean when to select one over the other  Srinika |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-05-24 : 10:58:38
|
| >>Where can I use Temp Table and where can I use Table Variable. Some ramplings:If you are going to insert to the table the results of any exec'd statement than you can't use a table variable. If your table will hold more than "a small amount" of data you will be better off with a temp table. Thousands of rows in a table variable can be extremely slow, especially joined to other tables.If you want to access the table accross multiple batches (intra-GOs) or if you want to manually execute portions of code at a time then temp tables are the way to go. Table variables - like any local variable - need to be re-declared and initialized everytime they are used. Within a single session a temp table is available until you drop it.Table variables used in SPs can reduce the activity in tempdb as well as reduce re-compiles. One of the things I use table variables a lot for is things like holding parsed values sent in to an SP by a comma seperated string. temp tables can be indexed or pretty much anything you can do with a permanent table (except things like RI)Be One with the OptimizerTG |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2006-05-24 : 14:47:24
|
| Thanks for the input TG.I dont have many rows, mostly less than 1000 at any given time.I preferred table variable because I dont see any need for index, the tempdb log / transaction would be less.ThanksKarunakaran |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
Temp Table Or Table Variable