Caution about Temporary Tables !

If you run the following statement, it will create a temporary table with Primary key named as "Pk_Col",

CREATE TABLE #Tmp (
col_01 INT CONSTRAINT Pk_Col PRIMARY KEY
)




I will open another connection using same username and run the same statement it should run successfully. However it will fail with following error.

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'Pk_Col' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

From the error message it is very clear that there is a object name conflict not the #Tmp, but the Pk_Col. Unlike #Tmp the Pk_Col is not treated as temporary object.

So to avoide such issue, and in the sprit of "good practice" don't give name to the constraint. If you don't give name to a constraint SQL Server will automatically give some arbitrary name like Pk_Col_A2340 which would never conflict if you rerun the command again and again. So the T-SQL should look like,

CREATE TABLE #Tmp (
col_01 INT PRIMARY KEY
)

Note that I have not given a name, so the primary key was named as PK__#Tmp______________1CF15040

Actually this issue occurred bit differently in my case. I was troubleshooting some T-SQL which was part of schedule job. While troubleshooting I ran the statement and everything looked fine. So went to Management studio and opened the job and pasted this modified T-SQL and executed the job. The job failed! - I looked at the job history and found that its failing due same error as above "Object name conflict". First I was surprise, b'cos both connections are separate and the primary key was on temporary table, but suddenly I realized the issue.

So, best option all the time is NOT to give a name to a constraints used for temporary tables (# tables). This behaviour is seen in SQL 2000 & 2005 both.