Adding a clustered index to a 100 million record table

Posted on 2011-04-26
Last Modified: 2012-05-11
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?  
Question by:soccerman767
    LVL 9

    Accepted Solution

    It is better at this stage you can use non-clustered index.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Table has only one cluster index. So you can create more than one non-cluster indexes.

    Author Comment

    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.
    LVL 17

    Expert Comment

    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.
    LVL 9

    Assisted Solution

    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.

    LVL 6

    Expert Comment


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now