Sql-server – Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime

ddlsql servertempdbtemporary-tables

Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler.

create proc spFoo
    @param bit
as
begin
    if @param = 0
    begin 
        select * 
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        select top 10 * 
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

Attempting the above returns the following error

Msg 2714, Level 16, State 1, Procedure spFoo, Line 19

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

In a human-readable sense, the proc appears to be fine: only one select into statement will ever be executed since they're wrapped inside the if-else blocks. Very well though, SQL server can't confirm that the statements are logically excluded from each other. Perhaps more confusing though is that the error remains when the drop table #foo is placed inside the if-else block ( which one assumes would tell the compiler to deallocate the object name ) as below.

create proc spFoo
    @param bit
as
begin
    select top 1 * 
    into #bar
    from [master].dbo.spt_values

    if @param = 0
    begin 
        drop table #bar;
        
        select * 
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        drop table #bar;
        
        select top 10 * 
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

The proc itself is fine. I sucked it up and wrote the create table #foo( ... ) and insert #foo ( ... ) statements, I'd been trying to skip with the select * into syntax. At this point, I'm just trying to understand why the compiler crapped out on me with the lazy-guy syntax. The only thing I can think of is that the DDL command reserves the object name IN TEMPDB.

Why the bold text?

create proc spIck
as
begin
    create table #ack ( col1 int );
    drop table #ack;
    create table #ack ( colA char( 1 ) );
    drop table #ack;
end;

This fails with the same error code as above. But the following…

create proc spIck
as
begin
    create table ack ( col1 int );
    drop table ack;
    create table ack ( colA char( 1 ) );
    drop table ack;
end;

… succeeds. The same follows above to the original proc attempt. So…

My Question Is This

What is the difference ( and why is it present ) in object name reservation for TempDB objects as opposed to user databases. None of the Logical Query Processing references nor DDL command references I've reviewed appear to explain this.

Best Answer

This has nothing to do with object name reservations in TempDB or anything to do with runtime. This is simply the parser not able to follow logic or code paths that assures that your code couldn't possibly try to create that table twice. Notice that you get the exact same (non-runtime!) error if you just click the Parse button (Ctrl+F5). Basically, if you have this:

IF 1=1 
  CREATE TABLE #foo(id1 INT);
ELSE
  CREATE TABLE #foo(id2 INT);

The parser sees this:

  CREATE TABLE #foo(id1 INT);
  CREATE TABLE #foo(id2 INT);

Why does it not work this way for actual tables, including actual user tables created in TempDB (note that it isn't database-specific, either)? The only answer I can suggest is that the parser has a different set of rules for #temp tables (there are a lot of other differences, too). If you want more specific reasons, you'll need to open a case with Microsoft and see if they'll give you any further details. My guess is you will be told: "this is the way it works."

Some more info in these answers:

Related Question