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.

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.

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.

I might be 9 years late but this is how I do it (this drops the existing index and creates the new one with the columns in the list):

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME]
(
	[COLUMN1] DESC,
	[COLUMN2] ASC
) 
WITH
(
	PAD_INDEX = OFF,
	STATISTICS_NORECOMPUTE = OFF,
	SORT_IN_TEMPDB = OFF,
	IGNORE_DUP_KEY = ON,
	DROP_EXISTING = ON,
	ONLINE = OFF,
	ALLOW_ROW_LOCKS = ON,
	ALLOW_PAGE_LOCKS = ON
) 
ON [PRIMARY]

I hope alter index means first we have to drop the index and create the index again

syntax:

if exists
(
select * from sys.indexes where name ='ix_name'
)
BEGIN
DROP INDEX Table.index name
END

IF NOT EXISTS
(
select * from sys.indexes where name ='ix_name'
)
BEGIN
CREATE NONCLUSTERED INDEX 
ON TABLENAME
(
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
--Whatever column u want to add
)
end
go