Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Adding a clustered index to a 100 million record table

SQL2005    I have a 25 gig ,100 million record table in my database without a clustered index. It has a index on the primary key that I want to change to a clustered index.

Is this something I can do while I am online? Is there any pitfalls you guys can warn me about?  
2 Solutions
It is better at this stage you can use non-clustered index.
Alpesh PatelAssistant ConsultantCommented:
Table has only one cluster index. So you can create more than one non-cluster indexes.
soccerman767Author Commented:
Sarabhai.... Why at this stage should i just stick with a non-clustered?

PatelAlpesh... Yes I know you can only have one clustered indexes. While I am not a DBA I have creted 100s, maybe thousands of indexes.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Chris MangusDatabase AdministratorCommented:
Because the clustered index is going to physically rearrange the data on disk this is going to take quite some time if your hardware isn't extremely robust.

I would only do this offline.
At this stage means your database is online if you drop existing and create new one it will take time during this time table is not available for transaction.

some guidance for you

    Create new table with a "temporary" name
    Create UPDATE, DELETE Trigger on existing Table(s) to capture changes
    Create SQL Agent Job that runs periodically to migrate existing rows over to the new Table.

    Rename object names for current Tables, Triggers, Indexes, Constraints to be "OLD"
    Rename "new" objects (Table, Triggers, Indexes, Constraints, etc.) to be the expected current name
    Update Stored Procedures, View, Functions that are effected
    Drop data migration SQL Job

    Drop "old" Table(s)

this is for your database is in online assumption.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now