What is the preferred method for creating, using and deleting temporary tables in sql server?

Question

When using temporary tables in stored procedures, SQL Server is the preferred practice:

1) Create a temporary table, fill it in, use it, then lower it

CREATE TABLE #MyTable ( ... ) -- Do stuff DROP TABLE #MyTable 

2) Check if it exists, discard it if that happens, then create and use it

 IF object_id('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable CREATE TABLE #MyTable ( ... ) 

3) Create it and let SQL Server clean it when it goes out of scope

 CREATE TABLE #MyTable ( ... ) -- Do Stuff 

I read in this answer and related comments that it can be useful in situations where the temporary table is reused so that SQL Server truncates the table but saves it to save time.

My stored process will most likely be called quite often, but it contains only a few columns, so I don’t know how beneficial this is in my situation.

Answer 1

You can test and see if one method is superior to the other in your scenario. I heard about this reuse guide, but I have not conducted any extensive tests myself. (My gut instinct should explicitly remove any #temp objects I created.)

In one stored procedure, you never have to check if a table exists - unless it is also possible that the procedure is called from another procedure that could create a table with the same name. This is why it is good to use #temp table names rather than using #t, #x, #y, etc.

Answer 2

I follow this approach:

 IF object_id('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable CREATE TABLE #MyTable ( ... ) // Do Stuff IF object_id('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable 

Cause. If some error occurs in sproc and the created temporary table is not discarded, and when the same sproc is called with an existence check, it will raise an error that cannot be created and will never succeed if the table is discarded. Therefore, always check the existence and object before creating it.

Answer 3

When using temporary tables, my preferred practice is actually a combination of 1 and 2.

  IF object_id('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable CREATE TABLE #MyTable ( ... ) // Do Stuff IF object_id('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable