Benefits of droping a temporary table

I typically drop #temp tables explicitly just out of habit (I like cleaning up anything I create). There is a myth out there that dropping a #temp table explicitly will prevent #temp table caching and reuse, but this is not true: See this blog post by @PaulWhite for a lot more details, and this one for even more.

#temp tables that are created in a child scope (Procedure, Trigger, EXEC-ed SQL) are automatically dropped when the scope ends.

But ones at @@NESTLEVEL of 0 are only automatically dropped when the session ends. These hang around for multiple batches.

So for such adhoc queries it is essential to drop them (especially if they might be run multiple times or a later batch will create one of the same name).

Pasting the following into a new query window in SSMS

CREATE TABLE #T (X INT PRIMARY KEY)

INSERT INTO #T VALUES (1)

Will fail on the second run with

Msg 2714, Level 16, State 6, Line 2

There is already an object named '#T' in the database.

You're explicitly telling the server to remove references to the #temp table. If the #temp table was using a lot of resources in, for instance, tempdb executing DROP TABLE #temp may reduce resource consumption prior to the end of the session.

You might want to drop the table to ensure it doesn't get erroneously used after its data has been invalidated.