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.

Thanks in advance.

Solution 1:

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.

Solution 2:

If the new column you are adding to the index is on the end of the list of columns - in other words, if the column list of the old index is a prefix of the column list of the new index - then rows which are sorted by the old columns will still be sorted by the new columns. In Sybase SQL Server and perhaps older versions of Microsoft SQL Server, there was a with sorted_data option to let you declare that the rows were already sorted. But on MSSQL 2008 R2 it appears to have no effect; the option is accepted but silently ignored. In any case I think the option was mostly useful with clustered indexes.

Others mentioned with drop_existing, which sounds great, but is for more expensive versions of MSSQL only.