CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlindexing
Tom
Tom

Reputation: 4577

Creating indexes with T-SQL returns index exists error

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.

Upvotes: 0

Views: 1386

Answers (1)

Oded
Oded

Reputation: 499002

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.

Upvotes: 7

Related Questions