Creating Duplicate Table in SQL SERVER 2014

Question

I am trying to create a duplicate table in SQL SERVER. I changed the table name but all other objects like constraints are same. Do i have to change all the objects as they exists in the base table or i can have same objects name? I know it might sound a silly question but i am new to this.

Answer 1

Explicitly named constraints must be uniquely named within a database.

If they are not unique, you'll see errors like this:

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

This is especially pertinent for creating #temp tables. Never specify the name of a constraint when creating a #temp table, since concurrent executions of the code will fail with the above error.

Take for example:

CREATE TABLE dbo.SomeTable
(
 ID int NOT NULL
 CONSTRAINT PK_SomeTable
 PRIMARY KEY CLUSTERED
);
CREATE TABLE dbo.SomeTable2
(
 ID int NOT NULL
 CONSTRAINT PK_SomeTable
 PRIMARY KEY CLUSTERED
);

The command fails because PK_SomeTable is defined twice, once in each table. However, if we re-code the 2nd statement:

CREATE TABLE dbo.SomeTable2
(
 ID int NOT NULL
 CONSTRAINT PK_SomeTable2
 PRIMARY KEY CLUSTERED
);

Now, it works.

For #temp tables, you'd use the following construct:

CREATE TABLE #SomeTable
(
 ID int NOT NULL
 PRIMARY KEY CLUSTERED
);