Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns. due to some reason if i need to add one more column in the existing index .
Do i need to rebuild index ?
Add a comment
|
1 Answer
When you add/remove any column in existing non-clustered index, the index will be recreated(not altered) and hence index rebuild is not required.
The larger your tables, the greater the impact of executing DDL statements against indexes, both in the consumption of server resources and in the slowing of other queries that are executing at the same time.
On Enterprise Edition, you can choose to use CREATE INDEX ... WITH (DROP_EXISTING = ON, ONLINE = ON) (for certain index changes at least) to minimize interference with other queries (they can continue using the original index while the new one is building, at the cost of the additional disk space / activity required to maintain both). And in 2019 you can use RESUMABLE so, you can pause the creation of the new index during high activity, or spread the operation across multiple maintenance windows.
answered Feb 27, 2020 at 11:20
