Partager via

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

  • Article

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

  • Anonymous
    April 28, 2007
    What are table variables? Can you elaborate on that. Thanks.

  • Anonymous
    April 29, 2007
    I meen you may try to use something like that declare @temp TABLE  (ID INT NOT NULL PRIMARY KEY, NUM INT)

  • Anonymous
    June 20, 2007
    The comment has been removed

  • Anonymous
    August 02, 2007
    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.

  • Anonymous
    December 14, 2007
    The comment has been removed