Explicityly naming a constraint on a temp table will cause an error as the constraint names have to be unique in the system tables.
SQL Server will add a unique suffix to the temp table name to allow multiple instances of that temp table to exist, but it does not do the same thing with explicitly named constraints on the temp tables, instead the constraint is named exactly as specified in the DDL.
Either explicitly named constraints on temp tables should behave the same way as the table name itself, i.e. a unique suffix is added, or it should not be allowed.
Interestingly enough table variables do not allow the PK constraint to be explicitly named.
CREATE TABLE #TempTest
(TestID INT IDENTITY CONSTRAINT PK_TempTest_TestID PRIMARY KEY CLUSTERED,
The temp table is created without issue. However if the same code is run under a different connection to the database while the first instance of the temp table still persists, the following error occurs:
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_TempTest_TestID' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have found this behaviour to be consistent in SQL Server 2000, 2005, 2008 & 2008 R2, with all SPs and regardless of whether SQL Server is in a VM environment or not.