Clever Geek Handbook

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?

+3


source to share


2 answers


You can change the definition of a clustered index (i.e. add a new column to the keys) as an online operation. I don't have an instance to check, but the syntax is as follows:

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


The operation is completely online (the table is open for reading, inserting, updating and deleting). You need an Enterprise Edition instance (since only EE supports online index creation operations), and the table cannot have LOB columns (at least not until SQL Server 2012 ).

The operation is a complete rebuild of a table, resource heavy and generating a significant log.

+2


source


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



You can do this online, but it can be cumbersome and time consuming if the table is large.

+1


source






More articles:


All Articles