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

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

Kris — Sun, 29 Apr 2007 04:33:35 GMT

What are table variables? Can you elaborate on that.

Thanks.

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

Igor Kovalenko — Mon, 30 Apr 2007 01:53:50 GMT

I meen you may try to use something like that

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

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

Mike — Wed, 20 Jun 2007 17:29:51 GMT

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.

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

Fitzer — Fri, 03 Aug 2007 14:44:31 GMT

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.

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

Ryan — Fri, 14 Dec 2007 18:28:08 GMT

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