Explicitly drop temp table or let SQL Server handle it

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

In a multi-threaded scenario where each thread creates its own set of tables and the number of threads is throttled, not dropping your own tables means that the governor will consider your thread done and spawn more threads... however the temp tables are still around (and thus the connections to the server) thus you'll exceed the limits of your governor. if you manually drop the temp tables then the thread doesn't finish until they've been dropped and no new threads are spawned, thus maintaining the governor's ability to keep from overwhelming the SQL engine

I used to fall into the crowd of letting the objects get cleaned up by background server processes, however, recently having issues with extreme TempDB log file growth has changed my opinion. I'm not sure if this has always been the case with every version of SQL Server, but since moving to SQL 2016 and putting the drives on a PureStorage SSD array, things run a bit differently. Processes are typically CPU bound rather than I/O bound, and explicitly dropping the temp objects results in no issues with log growth. While I haven't dug in too deeply as to why, I suspect it's not unlike garbage collection in the .NET world where it's synchronous when called explicitly and asynchronous when left to the system. This would matter because the explicit drop would release the storage in the log file, and make it available at the next log backup, whereas this appears to not be the case when not explicitly dropping the object. On most systems this is likely not a big issue, but on a system supporting a high volume ERP and web storefront with many concurrent transactions, and heavy TempDB use, it has had a big impact. As for why to create the TempDB objects in the first place, with the amount of data in most of the queries, it would spill over into TempDB storage anyway, so it's usually more efficient to create the object with the necessary indexes rather than let the system handle it automatically.

CREATE TABLE (Transact-SQL)

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

• A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
• All other local temporary tables are dropped automatically at the end of the current session.
• Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.