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) 


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?

    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
            WAITFOR DELAY '00:05:00'  -- Wait for 5 minutes
            RETURN 0

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


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? ♪♪♪] 

    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
            WAITFOR DELAY '00:05:00'  -- Stay awhile and listen

            RETURN 0


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


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!

Thanks for reading!


Share it:

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.