10

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.

Community
  • 1
  • 1
Hoppe
  • 6,508
  • 17
  • 60
  • 114
  • 1
    Within the applications I've built, I've never had to explicitly drop the temp table. The scope applied by SQL Server seems to have been more than sufficient. I've also never read/heard/learned about a best practice such as this regarding temp terminals. That said, I don't think it would hurt, either. – brazilianldsjaguar Mar 06 '15 at 18:38
  • This question may run down a rabbit trail of opinion. Ultimately, if you drop it after you use it, it will not be necessary to drop it before you need it, unless another process is creating the same table. And then you have a process issue. If I were to make a suggestion, it would be to use `IF NOT EXISTS` on the object itself rather than `IS NULL`. – Phoenix Mar 06 '15 at 18:39

2 Answers2

10

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.

Alsin
  • 1,514
  • 1
  • 14
  • 18
  • 1
    I tend to like this over doing neither as someone else suggested. That said, wouldn't an exception, raised inside the stored proc at any point after the "create #task_role_order...", potentially leave a temp table present for the next time the sproc is called (raising "There is already an object named '#task_role_order' in the database." error) ? If so, the IF EXISTS check at the top seems to a good practice, or perhaps a DROP TABLE in a Catch block? – Jeff Mergler Oct 10 '16 at 19:48
1

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.

Necreaux
  • 9,451
  • 7
  • 26
  • 43