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

Answer 1

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.

Answer 2

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.

Answer 3

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.