SQL 2000 to SQL 2005 migration: temporary tables reuse issue.

During the same project the next issue was found: temporary table reuse. Here is an example on “how to reproduce”:

USE tempdb

GO

CREATE PROCEDURE dbo.usp_step_one

AS

BEGIN

                CREATE TABLE #temp (ID INT NOT NULL, NUM INT)

                ALTER TABLE #temp ADD CONSTRAINT PK_ID PRIMARY KEY (ID)

                INSERT INTO #temp SELECT 1, 1

END

GO

CREATE PROCEDURE dbo.usp_step_two

AS

BEGIN

                CREATE TABLE #temp (ID INT NOT NULL, NUM INT)

                INSERT INTO #temp SELECT 1, 1

END

GO

BEGIN TRAN

EXEC dbo.usp_step_one

PRINT 'step one 1'

EXEC dbo.usp_step_one

PRINT 'step one 2'

EXEC dbo.usp_step_two

PRINT 'step two 1'

COMMIT

GO

DROP PROCEDURE dbo.usp_step_one

DROP PROCEDURE dbo.usp_step_two

GO

SQL 2000 will process this batch without errors, but SQL 2005 will get you the following results:

step one 1

Msg 2714, Level 16, State 4, Procedure usp_step_one, Line 6

There is already an object named 'PK_ID' in the database.

Msg 1750, Level 16, State 0, Procedure usp_step_one, Line 6

Could not create constraint. See previous errors.

The problem is in temporary tables caching during transaction. In SQL 2000 it always used to drop local temp tables as soon as they get out of scope. In SQL 2005, the drop is delayed until the transaction is gone (rollback / commit). You can find some more details about temp tables caching here. As a workaround you can:

- Use table variables instead of temporary tables

- Avoid named constraint creation on temporary objects like

CREATE TABLE #temp (ID INT NOT NULL PRIMARY KEY CLUSTERED, NUM INT)