February 28, 2008 at 3:08 am
Hi,
I've recently developed a stored procedure that uses temporary table (#temp_Variance).
Will there be problem if multiple users access the same stored procedure parallely (thro asp.net pages).
At the beginning of the procedure i' m using the snippet below.
select @tempVar = db_name -- gets my database name say SS_FLOW
set @tempVar = @tempVar + '#temp_Variance'
if not object_id(@tempVar) is null
BEgin
set @tempVar = 'DROP TABLE ' + @tempVar
exec (@tempVar )
End
--- After the above i'm going for usual processing ...
select Col1,Col2 ... into #temp_Variance from flw_fiscal
and atlast
i'm dropping the temp table.
Can any one advice me on this.
Thanks and Rgds,
PL.Seenivasan
February 28, 2008 at 3:31 am
Try it for yourself. Run the following code:
create table #MyTable(t int)
then create a new connection to the database and run the same code again. Now run this query:
SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#MyTable%'
You will see that the two temp tables were created entirely separately.
John
February 28, 2008 at 4:57 am
What you were thinking of was a global temp table - these take the form ##myTable. There are two hashes rather than just one. Temp tables prefixed with a single # are specific to each connection/session and are actually suffixed with a very long unique string so that when two tables of the same name from different connections are created in tempDB their overall name with the suffix will be different.
February 28, 2008 at 5:34 am
You can even go into the tempdb and watch the tables being created. You don't need to worry about the users stepping on each other. You do need to make sure your tempdb is ready for the increased load though. MS has a great white paper on the topic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 6:49 pm
Dear All,
Thank you so much for giving me a detailed clarification 🙂
PL.Seenivasan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply