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