Is it best to drop the temp table after using it in addition to creating the temp table?

Question

I have a saved proc that creates a temporary table. This is only necessary for the volume of the stored procedure, and not elsewhere.

When I use temp list tables, I always check if a temporary table exists, and discard it if that happens before creating it in a saved proc. I.e:.

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

In most cases, is it better to delete the temporary table when it is running with it, in addition to creating the temp table?

If more context is required, I have a .NET Web API end that calls stored procs in the database. I believe that SQL Server resets the temporary table when the SQL Server session ends. But I don't know if .NET will open a new SQL Server session every time it queries the database or only once for the application life cycle, etc.

I read this similar question , but thought it was a little different.

Answer 1

It is generally considered good practice to free a resource if you no longer need it. Therefore, I would add a DROP TABLE at the end of the stored procedure.

The temporary table works until the connection is down. Typically, applications use the connection pool (it is configured), and the connection does not close when Connection.Close is called. Before reusing the connection, the client performs a special stored procedure (sp_reset_connection), which performs all cleaning tasks. Therefore, temporary tables will be discarded anyway, but sometimes after some delay.

Answer 2

This is unlikely to have a big impact, but if I had to choose, I would not do that either. Temporary tables are accessible through nested stored procedures, so if you don’t have a specific need to transfer data between procedures without performing any of them, this will help to avoid conflicts, if you use the same name, call the procedure recursively in a circular way (and this ), or you have another procedure that uses the same name and columns. Removing it from practice can hide some strange logical errors.

For example, Proc A creates a temporary table, then calls B. B and creates a table. Now either Proc A now refers to the temporary table created, or since Proc A is not nested inside B, Proc Mysteriously fails. It would be better if proc B fails when it tries to create a temporary table.

At the end of the day, SQL Server will clear them, but that will not stop you from running between the nested procedures.