Menu
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.
329
Expand the table in which the index belongs and then expand Indexes. Right-click the index that you want to modify and then click Properties. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.
Procedure. To add a column to an existing index: Issue the ALTER INDEX ADD COLUMN SQL statement when you add a column to a table. Commit the alter procedure.
Right-click the table on which you want to create a unique index and select Design. On the Table Designer menu, select Indexes/Keys. In the Indexes/Keys dialog box, click Add. Select the new index in the Selected Primary/Unique Key or Index text box.
Expand the "General" tab. Make sure you have the column you want to make unique selected in the "columns" box. Change the "Type" box to "Unique Key". Click "Close".
You cannot alter an index - all you can do is
drop the old index (DROP INDEX (indexname) ON (tablename))
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.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
