QuickQuestion: Can you create two #SameName temp tables in a single session?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.

Today’s question:

Can you create two #SameName temp tables in a single session?

That question seems relatively easy to answer. Let’s try something:

CREATE TABLE #sameName (id int) 

GO 

CREATE TABLE #sameName (id int)

You definitely know what will happen here…

Boring example, expected result. Now that we got it out of the way, we can try something more interesting.

CREATE PROCEDURE [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] -- Can you do that?
AS 
BEGIN 

    CREATE TABLE #SameName (id int) -- Create #temp table

    INSERT #SameName -- INSERT value  
    VALUES ('') 
 
    IF @@NESTLEVEL = 10 -- If it's 10th nest level of recursive execution
        BEGIN
            WAITFOR DELAY '00:05:00'  -- Wait for 5 minutes
            RETURN 0
        END 

EXEC [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] -- Recursive execution of the sp

END 
GO  

EXEC [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] -- Testing time!

How many temp tables will be created here?
a) 0 #SameName with an error? What error?
b) 1 #SameName table with 10 rows?
c) 10 #SameName tables with 1 row?

Let’s have a peek into tempdb, while the SP is WAITFORing, which isn’t even a word (if it’s doing that at all).

Peek into tempdb (spoilers!)

There you go. 10 tables. Each subsequent execution of the stored procedure creates its own copy of the #SameName table, each with its own contents. So to answer the title question in short: you can.

So… is the sub-procedure aware of #SameName created a level higher? Can we do the opposite and reuse that to have the row inserted into one #SameName table 10 times? Let’s try this:

CREATE PROCEDURE [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] 
AS 
BEGIN 

    IF OBJECT_ID('tempdb..#Samename') IS NULL -- Check if #SameName exists
    CREATE TABLE #SameName (id int) -- If not, create one

    INSERT #SameName  
    VALUES ('') 

 

    IF @@NESTLEVEL = 10 -- If it's 10th nest level of recursive execution
        BEGIN
         
            WAITFOR DELAY '00:05:00'  -- Stay awhile and listen

            RETURN 0

        END 

EXEC [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] -- execute the procedure recursively

END 
GO  

EXEC [#♪♪♪ usp_AmIATemporaryProcedure? ♪♪♪] -- 3..2..1.. GO

Outcome: Success! Well, at least partially. Although we have one #SameName table with 10 rows, think about the guy troubleshooting your code at 2 AM trying to find out where did the values in a #temp table come from… In other words – just because you can doesn’t mean you should.

Now, armed with all that new and/or updated knowledge of how #temp tables behave, knowing that we can create a #TemporaryStoredProcedure named after a good [♪♪♪ tune ♪♪♪], let’s ask ourselves one last question: what values have actually been inserted into our table? Think twice…

Show it

Why?! Because
” = 0
Ask around, anyone will tell you that!