CODEWITHSUNDEEP
sql-serversql-server-2008
Sriram Subramanian
Sriram Subramanian

Reputation: 2753

Is it possible to add a column to an existing clustered index in sql server?

I would like to know if it is possible to add a column to an existing clustered index on a table in sql server 2008? If so, is it possible to do it online?

Upvotes: 4

Views: 5906

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294227

You can change the definition of the clustered index (ie. add a new column to the keys) as an online operation. I don't have an instance to validate, but the syntax is something like following:

create clustered index [indexname] on [table]([column], [column], [column], ...)
with (drop_existing =on, online = on);

The operation is fully online (table is open for reads, inserts, updates and deletes). You need an Enterprise Edition instance (because only EE supports online index build operations) and the table cannot have LOB columns (at least up to SQL Server 2012 it cannot).

The operation is a full rebuild of the table, heavy in resources and will generate significant log.

Upvotes: 2

MiMo
MiMo

Reputation: 11953

You cannot add a column to an existing index but you can drop the index and re-create it with the additional column.

You can do it online, but it could be a heavy and time-consuming operation if the table is big.

Upvotes: 1

Related Questions