Creating indexes with T-SQL returns index exists error

Question

What I'm trying to do is make a copy of a table using a SELECT INTO statement.

After the table is created I want to duplicate the indexes as well.

So the code I'm using is as follows:

SELECT * INTO TableCopy FROM Table

Then:

ALTER TABLE TableCopy ADD CONSTRAINT pkGUID PRIMARY KEY ([GUID])
CREATE INDEX ixIndexName ON TableCopy (CountryCode)

When I execute the index SQL, I get an error that the indexes already exist in the catalog. I didn't think index names had to be unique, I thought they could be duplicated across different tables.

And lo and behold if I create the indexes through management studio, it accepts the index names.

What am I missing here?

Thanks.

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

I didn't think index names had to be unique, I thought they could be duplicated across different tables.

No. They do have to be unique within a table/view.

When you execute within SSMS, it drops the existing index and creates a new one.

From CREATE INDEX (Transact-SQL) on MSDN:

index_name - Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database.

(emphasis mine)

However, pkGUID is not an index - it is a constraint, and these do have to be unique within a database.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 14, 2012 at 15:31

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Martin Smith Over a year ago

Index names don't have to be unique. Constraint names do.

Tom Over a year ago

I have two tables in my database with pkGUID as a primary key and when I check the properties, I don't see that SSMS has assigned a different name to either of them

Oded Over a year ago

@Tom - pkGUID is the name of a constraint not an index.

Oded Over a year ago

@Tom - You are confusing constraints with indexes. A constraint name has to be unique within a database. And index name doesn't. You need to give the primary key constraint a different name to the existing one. I assume SSMS does that.

Oded Over a year ago

@Tom - I see two identical index names. Open up the Keys nodes.

|

Collectives™ on Stack Overflow

Creating indexes with T-SQL returns index exists error

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related