CODEWITHSUNDEEP
sql-server-2008indexing
suhprano
suhprano

Reputation: 1733

sql server does deleting column rebuild index?

Anyone know what happens to the index when I drop a column from a table?

Does SQL Server rebuild that index? What if it's a clustered index?

I have a 150 million row table, and I need to drop a column. Will this rebuild the table if it's ordered as a clustered index? Or does the index just understand that one of its columns are dropped?

Upvotes: 2

Views: 1296

Answers (2)

TToni
TToni

Reputation: 9391

It depends on the column. If the column is used as index column in any index (clustered or nonclustered) you cannot drop it unless you drop the affected indexes first.

If the column is not used as an index column, you can drop it and the clustered index will not be rebuilt. However, the used space of your table also won't change until you rebuild the clustered index.

Edit: Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Upvotes: 2

Jeremy
Jeremy

Reputation: 1208

That's not something the index can "just understand". A composite index can only be used by the query engine if all the columns used in the index are used in the query. It's a multi-level index, with indexes to indexes (if that makes sense). The order of columns in the index definition structure the physical index that is built. You will have to drop this index and build a new one if you drop the column.

Upvotes: 0

Related Questions