TOP 1 WITH TIES

Last week I was investigating a peculiar issue reported by our testing team. They are receving an error like the following occassionally.

Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'PK_Temp' in the database.

It seems that the error occurs only when multiple users tries to access the same page. A concurrency issue.

I looked at the code of the procedure being called from the page. I found that a code segment like the following is causing the problem.

CREATE TABLE #Temp(id int NOT NULL)
ALTER TABLE #Temp ADD CONSTRAINT PK_Temp PRIMARY KEY(id)


But why? SQL Server is supposed to make a temp table name unique by adding a unique integer suffix.

I opened two seperate connections in Query Analyzer and execute the above code. Same Error!

It turns that even though the temprary table names automatically made unique between concurrent sessions, the constraint names still has to be unique across a database.

I ended up adding "Do not use named constraints on temporary tables" to the list of best pracices.

Alternatively you can define constraints on temporary tables either by specifying a nameless constructor when you create table, like,


CREATE TABLE #Temp(id int NOT NULL PRIMARY KEY)

Or by implicitly adding a constraint with the following syntax


ALTER TABLE #Temp ADD PRIMARY KEY(id)