Is it "legal" to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting 
(
    WidgetID bigint not null,
    Name nvarchar(100) not null,
    Value nvarchar(max) not null,
    CreateDate datetime not null
)

INSERT VALUES

MERGE TABLES

DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END

Msg 2714, Level 16, State 1, Line 8

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

And to prove it's not SSMS complaining at compile time (a common misconception):

DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END';

EXEC sp_executesql @sql;

Yields the exact same error, even though SSMS does not try to parse or validate dynamic SQL before sending it to the server via sp_executesql.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

• Can I recreate a temp table after dropping it?

StackExchange Database Administrators Q#42657, answer score: 11