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.



Did You find this page useful?

Yes No



Last Modified: 21 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function