CODEWITHSUNDEEP
sql-serverindexing
Bala
Bala

Reputation: 4547

How to speed up recreating cluster index

In SQL Server, there is no option for altering the cluster index if i want to add one new column to cluster index definition. The only option is to drop and create cluster index with new definition.

From what I understand, drop and create of cluster index is a very costly and time consuming for high volume tables.

Cluster index recreate rebuilds all the nonclustered indexes on a table which can be very expensive.

The question to this forum "is there anyway we can speed up cluster index recreating"

The one workaround what I can think is to drop all non-cluster index before recreating cluster index. Will this approach work ?

Upvotes: 3

Views: 1206

Answers (1)

Martin Smith
Martin Smith

Reputation: 453298

Use 

CREATE .... WITH (DROP_EXISTING = ON)

Instead of 

DROP ... 
CREATE ...

This means the non clustered indexes only have to be updated once (to include the new key column). Not twice - first to use the physical rid and then again to use the new CI key.

The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place..

Additionally, SQL Server won't rebuild the nonclustered index at all if the clustered index key doesn't change and is defined as UNIQUE, which isn't an obvious performance benefit of defining a clustered index as UNIQUE

Example

CREATE TABLE #T
(
A INT,
B INT,
C INT
)

CREATE CLUSTERED INDEX IX ON #T(A)

CREATE CLUSTERED INDEX IX ON #T(A,B) WITH (DROP_EXISTING = ON)

DROP TABLE #T

Upvotes: 4

Related Questions