How Temp Table works in Stored Procedures (global & local )

Question

Image #1: I am creating a stored procedure for inserting records into #t1. In the same session I am executing the Localtemp1 stored procedure for any number of times, and it works fine:

Image #2: again executing the stored procedure in another session & works fine as well:

Image #3: now creating stored procedure for inserting records into ##tt. For the first execution of globaltemp1 stored procedure, it works well:

Image #4: but when I executed it a second time, it is showing errors (does not exist in DB):

Image #5: then I closed the session where globaltemp stored procedure was created, and in a new session, I executed the stored procedure, and it works well for the first time:

Image #6: but when I execute it a second time, again it is showing errors (does not exist in DB):

What I know is scope of local temp & global temp, but in stored procedures, they were completely different

Can someone tell me

1. Execution of localtemp1 stored procedure for many times gives output but while executing globaltemp1 sp for the first time gives output and second time results in an error

2. As far as I know, after execution of stored procedure temptable gets dropped. Then why localtemp1 stored procedure is getting executed across all sessions and many number of times?

3. Why globaltemp1 stored procedure is executing once and for second time showing an error?

4. Final one, Globaltemp stored procedure shows output in another session for the first time only when created session was closed

I mean

• 56 ----> globaltemp sp was created
• 57 ----> to get o/p i need to close 56
• 58 ----> to get o?p i need to close 57 ( WHY ??? )

I am a beginner at SQL and please, someone make me understand because if I don't find logic & correct reason I could not dive into another topic.

Answer 1

The concept of temp table is to hold records temporarily. It's some kind of an array where you can store multiple records using the same variable.

When you create a Temp Table, actually it is being created in the tempdb of the corresponding server. Even if you are naming it as just #temp, the name on which it was created on the tempdb will be having some additional parameters like your database name from which the table was created and your session id etc.

I just created the following temp table in my master database

and this is how it was named in the tempdb

still, in my database, I can access it using the name #temp. But The limitation of such temp table is that they are local and can be accessed only from that session, So if I try to access this #temp from any other Query Window (Session) even on the same database, I won't be able to access it. That's where we use Global temp tables. So If I add one more # to the table name then it becomes global temp table which can be accessed across the sessions. It is still created on the Tempdb but like this

Whenever you close the query window/session both Local and Global temp tables are automatically dropped.

So in the case of stored procedures, the starting and ending time of the sp is treated as one session. So once the sp execution is completed all the temp tables created inside that sp is dropped. So you can not use one temp table that was created by one SP in another one.

Hope this helps

Answer 2

Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.