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)


 

Comments (5)
  1. Kris says:

    What are table variables? Can you elaborate on that.

    Thanks.

  2. Igor Kovalenko says:

    I meen you may try to use something like that

    declare @temp TABLE  (ID INT NOT NULL PRIMARY KEY, NUM INT)

  3. Mike says:

    We ran into the same thing in our production code. I was nasty to trace because they were nested stored procedures using the same #table name with differing schema (not my design).  The whole thing makes seems analogous to c# and garbage – delayed.

  4. Fitzer says:

    Rather than use a primary key , consider using a clustered key and precede the name with  a #

                   CREATE CLUSTERED INDEX [#temp_CIDX] ON #temp

    (

    [ID] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    You will not have the Primary key constraint feature but you will have the benefit from indexing and stats.

  5. Ryan says:

    Great suggestion.  I had this same issue however unfortunately for me the additional challenge that my clustered PK key had to be on 2 columns, in which case you do have to name the constraint.  In order to avoid a naming conflict for that constraint my solution was to dynamically execute the DDL code to create the table, this way I created a unique constraint name on the fly.  You have to run dynamic sql, but sometimes that’s just what works.  You’ll end up with your PK constraint and no naming conflicts.

    DECLARE @CreateString nvarchar(512)

    SET @CreateString = N’CREATE TABLE ##temp

       ([ID_ONE] int NOT NULL, [ID_TWO] int NOT NULL, [NUM] int,

       CONSTRAINT [PK_ID_’ + Cast(NEWID() as nvarchar(50)) + ‘] PRIMARY KEY CLUSTERED

           ([ID_ONE], [ID_TWO]) WITH (IGNORE_DUP_KEY = OFF)) ON [PRIMARY]’

    exec sp_executesql @CreateString

Comments are closed.

Skip to main content