SQL Server: how to write an alter index statement to add a column to the unique index?

sql-server-2005 indexing alter
JL. picture JL. · Mar 19, 2012 · Viewed 87.3k times · Source

I have a UNIQUE, NON CLUSTERED index on a table that is currently using 4 columns for the index.

I want to create an alter script that can merely add another column to this index. The new column type is varchar.

The database is SQL Server 2005.

Thanks in advance.

Answer

marc_s picture marc_s · Mar 19, 2012

You cannot alter an index - all you can do is

  1. drop the old index (DROP INDEX (indexname) ON (tablename))

  2. re-create the new index with the additional column in it:

       CREATE UNIQUE NONCLUSTERED INDEX (indexname)
   ON dbo.YourTableName(columns to include)

The ALTER INDEX statement in SQL Server (see docs) is available to alter certain properties (storage properties etc.) of an existing index, but it doesn't allow changes to the columns that make up the index.

Related questions

Why use the INCLUDE clause when creating an index?

While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways. CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) -- OR -- CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, …

Read More
How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am …

Read More
Unable to create index because of duplicate that doesn't exist?

I'm getting an error running the following Transact-SQL command: CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName ON DimMeasureTopic(TopicShortName) The error is: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object …

Read More