Re-indexing large table - how screwed am I?

Question

I have a 1 TB, 600m row, table which has a misguided choice of indexed columns, specifically a clustered index on the primary key column which is never used in a select query.

I want to remove the clustered index from this row and create it on a number of other rows.

Table is currently like this:

• colA (PK, nvarchar(3)) [clustered index pt b]

• colB (PK, bigint) [clustered index pt a]

• colC (DateTime) [non-clustered index]

• colD (Money) [non-clustered index]

• colE (bit) [no index]

• colF (bit) [no index]

• colG (int) [no index]

• more non-indexed columns

I would like to change it to look like this:

• colA (PK, nvarchar(3)) [clustered index pt a]

• colB (PK, bigint) [non-clustered index]

• colC (DateTime) [non-clustered index]

• colD (Money) [clustered index pt d]

• colE (bit) [clustered index pt b]

• colF (bit) [clustered index pt c]

• colG (int) [clustered index pt e]

• more non-indexed columns

Two questions: 1) How long would you guesstimate that this change will take (server spec at end of message). Unfortunately it is a live DB and I can't have downtime without some idea of how long it will be down for.

2) Is it a terrible idea to add so many columns to a clustered index? Updates are nearly never performed. There are many inserts and many selects which always use all of the proposed indexed rows as select parameters.

Server spec: 5 x 15kRPM drives in RAID 5, MS-SQL Sever 2005 and some bits to keep them running.

Answer 1

For one thing, I would AVOID making the clustered index wider than it absolutely has to be. Making it into five parts seems about contra-productive. Are ALL the columns in this compound clustered index stable, e.g. never change??

If not, I would avoid them at all costs. A clustered index should be:

• unique
• stable
• as narrow as possible

You can change your non-clustered indices - no problem. But avoid making the clustered index messy! That'll definitely bring down your performance!

Check out Kimberly Tripp's excellent blog articles on indexing:

• main link here
• best practices for clustering index here

Marc