Recently, I came across the same question a couple of times around usage of temporary tables in one of the forums I participate in. The questions revolved around attempting to create/use temporary tables in the various scenarios within the same batch:

1. Same temporary table name, but a different definition
2. Create the temporary table conditionally with the same definition multiple times (i.e. trying to create the temporary table only if necessary)
3. Create the temporary table conditionally with possibly different definitions (i.e.  in cases when the definition is unknown, e.g. data dumps/exports, etc)

In all of these cases, users were encountering the following error:

Msg 2714, Level 16, State 1...
There is already an object named '#tableName' in the database.

The scenario can be recreated in your development/study environment using the script provided below.

USE AdventureWorks2012;
GO
--Safety Check 
IF OBJECT_ID('#intermediateDataStore','U') IS NOT NULL
BEGIN
    DROP TABLE #intermediateDataStore;
END
GO

DECLARE @isEmployeeList BIT = 1;

--Generate a list of employees, 
--else generate a list of customer contacts
IF (@isEmployeeList = 1)
BEGIN
    SELECT pp.BusinessEntityID,
            pp.FirstName,
            pp.LastName
    INTO #intermediateDataStore
    FROM Person.Person AS pp
    INNER JOIN HumanResources.Employee AS he 
        ON pp.BusinessEntityID = he.BusinessEntityID;
END
ELSE
BEGIN
    SELECT pp.BusinessEntityID,
            pp.FirstName,
            pp.LastName
    INTO #intermediateDataStore
    FROM Person.Person AS pp
    LEFT OUTER JOIN HumanResources.Employee AS he 
        ON pp.BusinessEntityID = he.BusinessEntityID
    WHERE pp.BusinessEntityID IS NOT NULL
        AND he.BusinessEntityID IS NULL;
END

SELECT BusinessEntityID,
        FirstName,
        LastName
FROM #intermediateDataStore;
GO

Root Cause

In the context of non-temporary user tables, the root cause is quite simple – one has another table with the same name already created in the database.

However, in the context of temporary tables, root cause of the behaviour is a SQL Server design aspect called “Deferred Name Resolution” (DNR) (it  has nothing to do with a medical protocol that shares the same abbreviation).

What is Deferred Name Resolution (DNR)?

If one observes carefully, the statement failed at the time of parsing, and not during execution (one can easily verify this by trying to generate an estimated execution plan, which will also fail).

Here are the sequence of events that caused the script to fail during parsing due to deferred name resolution.

1. Drop any existing temporary tables with the name #intermediateDataStore
   • Compiles OK
2. Definition of variable @isEmployeeList
   • Compiles OK
3. First SELECT…INTO statement for storing a simple list of employees
   • Temporary table does not exist here, and hence is marked for DNR
4. ELSE block – Second SELECT…INTO statement for storing a simple list of non-employees/contacts
   • Temporary table does not exist here, and hence attempt is made to mark for DNR
   • However, another temporary table with the same name is already marked for DNR within the same batch, resulting in a conflict

Why is DNR required?

Deferred Name Resolution is not a bug, but a design feature – it allows SQL Server database engine to continue with the parsing of the batch with information that it can only get at runtime.

In the case of temporary tables, they have a slightly different actual name during execution. It is therefore impossible to have an idea about what the actual name of the table will be during compilation, which is why the name resolution needs to be deferred.

This is also the reason why stored procedures may show compilation errors only when executed for the first time, and not during deployment.

The Solution

There are multiple ways to arrive at the solutions to this problem, but all solutions revolve around the following two points:

1. Either separate the offending statements into different batches OR
2. If the structure is known to be the same/similar, try to define the structure of the temporary table in advance and just refer the already created instance of the temporary table when required

In our case, it is possible to know the structure of the temporary table in advance and hence the solution for our problem is as below:

USE AdventureWorks2012;
GO
--Safety Check 
IF OBJECT_ID('#intermediateDataStore','U') IS NOT NULL
BEGIN
    DROP TABLE #intermediateDataStore;
END
GO

--Because the structure is known, create the temp table here
--Else, one can try to split the application logic into 
--multiple distinct parts 
CREATE TABLE #intermediateDataStore 
    (BusinessEntityID INT           NOT NULL,
     FirstName        NVARCHAR(100)     NULL,
     LastName         NVARCHAR(100)     NULL
    )

DECLARE @isEmployeeList BIT = 1;

--Generate a list of employees, 
--else generate a list of customer contacts
IF (@isEmployeeList = 1)
BEGIN
    --Rather than using a SELECT...INTO, we are now
    --using an INSERT INTO...SELECT
    INSERT INTO #intermediateDataStore (BusinessEntityID,
                                        FirstName,
                                        LastName
                                       )
    SELECT pp.BusinessEntityID,
            pp.FirstName,
            pp.LastName
    FROM Person.Person AS pp
    INNER JOIN HumanResources.Employee AS he 
        ON pp.BusinessEntityID = he.BusinessEntityID;
END
ELSE
BEGIN
    INSERT INTO #intermediateDataStore (BusinessEntityID,
                                        FirstName,
                                        LastName
                                       )
    SELECT pp.BusinessEntityID,
            pp.FirstName,
            pp.LastName
    FROM Person.Person AS pp
    LEFT OUTER JOIN HumanResources.Employee AS he 
        ON pp.BusinessEntityID = he.BusinessEntityID
    WHERE pp.BusinessEntityID IS NOT NULL
        AND he.BusinessEntityID IS NULL;
END

SELECT BusinessEntityID,
        FirstName,
        LastName
FROM #intermediateDataStore;
GO

Conclusion

When I was researching the solution, it actually reminded me of the Single-Responsibility Principle of the SOLID design principles. The DNR problems demonstrated in this example would never have come up if the application had two separate modules – one to report for employees and the other to report for contacts.

Other than the obvious conclusion about the solution to the deferred name resolution message, the hidden conclusion is that design and architectural principles are independent of the programming language and platform – they stand equally true whether you are developing a piece of C# code, or writing a T-SQL stored procedure.

Further Reading

Deferred Name Resolution

• Microsoft TechNet: Deferred Name Resolution and Compilation [Link]
• Microsoft Connect: Creating a temporary table within a stored procedure with the same name as a temporary table created in the calling stored procedure [Link]

Fun with Temporary Tables

• Temporary Table Names – maximum allowed length [Link]
• Working with Temporary Tables (L100) [Link]
• Temporary Tables and the impact of ANSI_NULLS_DFLT_ON [Link]
• Temporary Tables and Foreign Keys [Link]
• Temporary Tables – Named Constraints, Msg 2714, 1750 [Link]
• Stored Procedures and Temporary Tables [Link]

Until we meet next time,
Be courteous. Drive responsibly.