Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘<objectname>’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.

Grant Fritchey (6/10/2009)

---

Well done. Extremely thorough, accurate, well written... but what's with the attached Word file?

Todd Johnson (6/10/2009)

---

I enojyed reading your article. It verfied some of the rumors I have heard over the years.

I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."

Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?

Todd Johnson (6/10/2009)

---

I enojyed reading your article. It verfied some of the rumors I have heard over the years.

I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."

Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?

I was wondering if I have been doing something wrong over the years. I typically used global temp tables to build cross-tab queries in the database for large amounts of data.

Thanks again,
T-Odd

create table ##T (
ID int primary key);

insert into ##T (ID)
select 1;

raiserror('Deliberate error', 20, 1) with log;

select *
from ##T;