Saturday, January 26, 2008

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 (

    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.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.