Normally temporary table's are only visible to the current connection that they are originally created on. However this is not the case. This can lead to a race situation occurring between multiple connections using temp tables. Or worse between applications that are using the same constraint name in two separate temp tables.

If you run the following code on a connection and then on a 2nd connection (in any other database) it will fail!

IF (OBJECT_ID('tempdb..#Import') IS NOT NULL)
      DROP TABLE #Import

CREATE TABLE #Import (
      ID int
      CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
      (
            [ID] ASC
      )
)

The code above will fail with the following error message.

There is already an object named 'PK_ID' in the database. Could not create constraint. See previous errors.

So there we have it. In MSSQL server 2005/2008 normal temp table's are not just visible to the current connection. The constraint name is global in the temp table. Of course the solution to this problem is easy. That would be to use a
guid for the constraint name.