Best way to change clustered index (PK) in SQL 2005

Question

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows:

ALTER TABLE Table ADD  CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
(
  [ColA] ASC,
  [ColB] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

I want to remove this clustered index PK and add a clustered index like follows and add a primary key constraint using a non-clustered index, also shown below.

CREATE CLUSTERED INDEX [IX_Clustered] ON [Table] 
(
  [ColC] ASC,
  [ColA] ASC,
  [ColD] ASC,
  [ColE] ASC,
  [ColF] ASC,
  [ColG] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,     DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

ALTER TABLE Table ADD CONSTRAINT
  PK_Table PRIMARY KEY NONCLUSTERED 
  (
    ColA,
    ColB
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I was going to just drop the PK clustered index, then add the new clustered index and then add the non-clustered primary key index, but I learned that dropping the existing clustered index would cause the table data to be reordered (see answer here What happens when I drop a clustered primary key in SQL 2005), which I don't think should be necessary. The table is knocking 1 TB, so I really want to avoid any unnecessary reordering.

My question is, what is the best way to go from the existing structure to the desired structure?

EDIT: Just want to clarify. The table is 1TB and I unfortunately do not have space to create a temporary table. If there is a way to do it without creating a temp table then please let me know.

Answer 1

If your table is getting up to 1 TB in size and probably has LOTS of rows in it, I would strongly recommend NOT making the clustered index that much fatter!

First of all, dropping and recreating the clustered index will shuffle around ALL your data at least once - that alone will take ages.

Secondly, the big compound clustered index you're trying to create will significantly increase the size of all your non-clustered indices (since they contain the whole clustered index value on each leaf node, for the bookmark lookups).

The question is more: why are you trying to do this?? Couldn't you just add another non-clustered index with those columns, to potentially cover your queries? Why does this have to be the clustered index?? I don't see any advantage in that....

For more information on indexing and especially the clustered index debate, see Kimberly Tripp's blog on SQL Server indexes - very helpful!

Marc