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.

Answer 3

If you have absolutely have to have a global temp table available your best solution would be to create a permanent table and then drop the table at the end of the stored procedure. You can check for it's existence before creating it:

IF OBJECT_ID('dbo.yourtablenamehere', 'U') IS NOT NULL DROP TABLE dbo.yourtablenamehere;

The differences between a temp table and a permanent table are really not that much different, mostly that the temp table drops automatically. If you are using this in an application that is calling this procedure, it might be best to have the application load the temp table into array and do the comparisons for you since it can maintain the array while executing and re-executing stored procs.

Answer 4

There is a good reason to write to a temp table instead of creating a table then dropping it... access rights. If you are creating tables to be used by people who are only granted read only privileges, they will not be able to create the table.