Temporary Table Scope?

Question

I am making use of temporary tables #tempTable in my stored procedure - that I make use to run my ASP.net Reports (Reporting services)

I am doing something like

eg. Code

SELECT * INTO #tempTable FROM Contacts WHERE ContactID < 10

Then I use something like

SELECT o.* FROM #tempTable t INNER JOIN Orders o ON t.ContactID =o.ContactID

to return values to my reports aka results for the stored procedure

I do not get rid of my #tempTable

i.e. I don't do

DROP TABLE #tempTable

I have read that the scope of temporary table is only for the stored procedure - so is doing the above necessary - if I dont do the above what problems will I get into in the future

Solution 1

First, local temporary tables created within a procedure are dropped once the procedure finishes. From the BOL on Create Table:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

If your data access code is properly opening a connection, calling a stored procedure and then closing the connection, the temp table is created in the procedure is effectively destroyed.

I say "effectively" to bring up another point. I would not recommend dropping the temp table at the end of your procedure although I would add a check just before I created the temp table and drop it if exists (e.g. if object_id('tempdb..#Foo') is not null). The argument against dropping the temp table at the end is that by calling the Drop statement, you are forcing SQL Server to expend resources to destroy the table then and there while you wait for your procedure to end. If instead, you let it go out of scope, your procedure ends immediately and you let SQL Server destroy the table at a time of its own choosing.

Solution 2

The #Temp table is limited scope to YOUR SESSION and lifespan of the batch, meaning nobody else can see your temp table and anyone else can create their own #Temp table with the same name. Once your session or batch ends, SQL Server will clean up the temp table.

On another note the ##Temp table behaves like a normal table. Everyone can see it, and there cannot be more than 1 ##Temp table with the same name. SQL Server will clean these ##Temp tables when the server restarts.

Solution 3

It's considered good coding practice to explicitly drop every temporary table you create. If you are executing scripts through SQL Server Management Studio/Query Analyzer the temp tables are kept until you explicitly drop them or until you close the session.

Solution 4

In general, you're probably not going to have problems by not dropping temporary tables. The local temp table has session scope, or SP scope in your case. It will drop automatically when the session is closed or the SP completes.

However, you do increase the risk of problems by regularly following this practice. For example, if you don't use an SP, but submit your SELECT statement from ASP .net and leave the SQL Server connection open, the temp table will continue to exist. Continued use of the connection and other temp tables will result in tempdb growth over time.

I also support the other comments regarding the use of temp tables in this case. If you create a solution without temp tables, you'll probably have a faster report and avoid the DROP temp table command too.

View more solutions

Comments

[soldieraman]

But if I am calling them through an ASP.net application?

[Myles J]

A local temp table that is created in a stored procedure is dropped automatically when the stored procedure completes. However, I tend to drop them as soon as I no longer need them. I guess this is more important in complicated long running stored procs.

[ean5533]

This is not completely accurate. Temp tables are NOT limited to the scope of the batch -- they survive for the entire session regardless of batches. That is, you can create a temp table, use GO, and the temp table will still exist with its data.

[Triynko]

I'm running into a situation where a temp table created in a .NET SqlCommand persist will not persist through to a 2nd SqlCommand, even though they're run on the same open connection.