Temp table mystery: Getting SQL server Error# Msg 2714 & 1750 there is already an object named #temp table ('Pk_id') in the database & could not create constraint. See previous errors.

 We have a sp in which we have created local temp table and this SP is used in the worker process (micro service developed in the c#). This process call this SP. In a millisecond it makes multiple hit.

 We have created the primary key constraint in the temp table. Some time we are getting the below error.  

Let’s replicating this issue

We are creating same temp table in two query windows.

IF Object_id('tempdb..#t', 'U') IS NOT NULL   DROP TABLE #t go CREATE TABLE #t   (      id   INT IDENTITY(1, 1) NOT NULL,      NAME VARCHAR(100),      CONSTRAINT pk_id PRIMARY KEY CLUSTERED (id)   )

When we run this script in first window the temp table is created successfully. 

When we are running this same code in other window we will get the below error.    

The reason behind it: we can’t create two constraints with the same name.  In the above scenario we are creating two temp table but we are trying to create the Primary key constrain with the same name (Pk_id). Due to this we are getting this error.

 To overcoming this issue below are the approaches

·         Create temp table without named constraint

·         Use table variable if we are getting less than 1000 records.

See here we are creating the temp table without named constrain.

IF Object_id('tempdb..#t', 'U') IS NOT NULL   DROP TABLE #t go CREATE TABLE #t   (      id   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,      NAME VARCHAR(100)   )

Running this script in the first window.  

Running the same script into the other window  

Now we are not getting the error.

 If we use the variable table in this case we will not get such type of error.

See the example of the variable table.

DECLARE @t TABLE   (      id   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,      NAME VARCHAR(100)   )

Running the code in the first window  

Running this code in the second window 

Now we are not getting the error.