Menu
I have a clustered index on a table indexing on the text column. I want to switch that column with a different column like ID, how do I alter the index?
I can't drop and recreate because this is running on Azure and the table needs to have clustered index all the time.
The SQL command and the syntax for changing index columns in an index.
alter index ?
488
You cannot alter a clustered index. The only option is to drop it and re-create it with the new column. In your case, you'll probably have to re-create the table with the new clustered index on ID and then copy the data over.
The SQL command and the syntax for changing index columns in an index. alter index ? You cannot alter a clustered index. The only option is to drop it and re-create it with the new column. In your case, you'll probably have to re-create the table with the new clustered index on ID and then copy the data over.
You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified. Limitations and Restrictions When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups.
Clustered indexes are implemented in the following ways: When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index.
Try this:
create clustered index [your_index_name]
on [your_table]
([ID])
with (drop_existing = on)
You cannot alter a clustered index.
The only option is to drop it and re-create it with the new column.
In your case, you'll probably have to re-create the table with the new clustered index on ID and then copy the data over.
35
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
