Explicitly drop temp table or let SQL Server handle it

Question

What is best practice for handling the dropping of a temp table. I have read that you should explicitly handle the drop and also that sql server should handle the drop....what is the correct method? I was always under the impression that you should do your own clean up of the temp tables you create in a sproc, etc. But, then I found other bits that suggest otherwise.

Any insight would be greatly appreciated. I am just concerned I am not following best practice with the temp tables I create.

Thanks,

S

Answer 1

My view is, first see if you really need a temp table - or - can you make do with a Common Table Expression (CTE). Second, I would always drop my temp tables. Sometimes you need to have a temp table scoped to the connection (e.g. ##temp), so if you run the query a second time, and you have explicit code to create the temp table, you'll get an error that says the table already exists. Cleaning up after yourself is ALWAYS a good software practice.

EDIT: 03-Nov-2021

Another alternative is a TABLE variable, which will fall out of scope once the query completes:

DECLARE @MyTable AS TABLE (     MyID INT,      MyText NVARCHAR(256) )  INSERT INTO     @MyTable VALUES     (1, 'One'),     (2, 'Two'),     (3, 'Three')  SELECT     * FROM     @MyTable