question

aRookieBIdev avatar image
aRookieBIdev asked

Parallel execution of stored proce that creates temp tables

I have a stored procedure which takes input parameter as clientid and loads data for the clientid. It uses several Local temporary table (names that begin with #) in the process. Can I execute the stored procedure for different clientids at the same time parallely using a same account ?
sql-server-2008temporary-table
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As per the section "Local Temporary Table" in the above link, > Local Temporary tables are private to the process that created it. and more detailed information in the third paragraph of that section.
1 Like 1 ·
It is all Local temporary table (names that begin with #)
0 Likes 0 ·
does it depend on the account executing it ? because i would be executing with the same account in parallel.
0 Likes 0 ·
No, the process.
0 Likes 0 ·

1 Answer

· Write an Answer
srutzky avatar image
srutzky answered
Local Temporary tables (i.e. starting with a single `#` and not a double `##`) are known only to the session that created them. A different session_id / SPID, or even 100 different sessions, can have the same `#TempTableName` and they will all be kept separate. The separation is handled by adding a "unique" value to the end of the "real" table name. This is easy to test. Just run the following in one query tab in SSMS: CREATE TABLE #Temp1 (Col1 INT); SELECT [object_id], [name] FROM tempdb.sys.tables; Then open up a new query tab to the same Instance and run it again. You should see something like the following: object_id name ----------- ---- -1489531709 #Temp1__________________________________________________________________________000000000089 -1457531595 #Temp1__________________________________________________________________________00000000008B
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the example
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.