Is it a best practice to drop the temp table after using it, in addition to before creating the temp table?

sql-server temp-tables sql-server-2014
Hoppe picture Hoppe · Mar 6, 2015 · Viewed 8.5k times · Source

I have a stored proc that creates a temp table. It is only needed for the scope of this stored proc, and no where else.

When I use temp tables list this, I always check to see if the temp table exists, and drop it if it does, before creating it in the stored proc. I.e.:

IF OBJECT_ID('tempdb..#task_role_order') IS NOT NULL    
        DROP TABLE #task_role_order        
CREATE TABLE #task_role_order(...)

Most of the time, is it a best practice to drop the temp table when done with it, in addition to before creating the temp table?

If more context is needed, I have a .NET Web API back end that calls stored procs in the database. I believe that SQL server drops the temp table when the SQL Server session ends. But I don't know if .NET opens a new SQL Server session each time it queries the database, or only once per application lifecycle, etc.

I've read this similar question, but thought that it was slightly different.

Answer

Alsin picture Alsin · Mar 6, 2015

Usually, it is considered a good practice to free up resource as long as you don't need it anymore. So I'd add DROP TABLE at the end of stored procedure.

Temporary table lives as long as connection lives. Usually, applications use connection pooling (it is configurable) and connection doesn't close when you call Connection.Close. Before connection re-usage, client executes special stored procedure (sp_reset_connection) which does all clean-up tasks. So temp tables will be dropped in any case, but sometimes after some delay.

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on …

Read More