sql-server-2005indexingalter

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

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.

Solution

  • 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.