October 16, 2012 at 10:44 am
Can you help me with this scenario, please.
A procedure p1 declares and uses a temp table #temp1.
Then 2 instances of the stored procedure p1 are run at the same time.
Can it lead to a situation when one of the procs will fail, because a temp table with a duplicate name will be attempted to be created on TEMPDB database?
thanks,
October 16, 2012 at 10:50 am
If it is a regular temp table ie #Temp, then No, Temp tables are specific to each connection. so different connections cannot share a single temp table.
If your using global temp tables ie ##Temp then yes this could be a problem.
Try it, Open 2 windows in SSMS.
Run this in each window.
Create table #Temp(ID INT Identity, Myval Varchar(100))
Runs fine.
October 16, 2012 at 10:53 am
Check out this article from Phil Factor. http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2012 at 10:57 am
Thank you , that explains it.
October 16, 2012 at 1:24 pm
my DBA got me confused again, he is telling me:
***You should be creating your temp tables within the user database and not within the ‘tempdb’ database.***
I guess at the moment I do not know the difference. I thought that if I execute smth like
select 1 as me
into #temp1
from myuser table
in a stored procedure that runs in my user database, #temp1 will be created in TEMPDB database. Is that correct?
And how can I create a temp table within my user database, if it's even possible?
Thanks,
October 16, 2012 at 1:29 pm
Temp tables are created in tempdb. That is how it works. Easy enough to demonstrate.
create table #MyTable ( ID int )
select * from tempdb.sys.objects
where name like '#MyTable%'
drop table #MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2012 at 1:47 pm
THANKS everyone for your help,
thread is closed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply