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

Question

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 1

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.

Answer 2

It's very unlikely to be of much impact, but if I had to choose I would do neither. Temporary tables are accessible via nested stored procedures, so unless you have a specific need to pass data between procedures, not doing either will help avoid contention if you happen to use the same name, call a procedure recursively, in a circular manner (and it is valid), or you have another procedure that happens to use the same name and columns. Dropping it out of practice could hide some weird logic errors.

For example Proc A creates temporary table, then calls B. B drops and creates the table. Now either Proc A now is referencing the temporary table created, or since Proc A is not nested inside B, Proc A mysteriously fails. It would be better to have proc B fail when it tries to create the temp table.

At the end of the day SQL Server will clean these up, but it won't stop you from leaking between nested procedures.