Post reply

Change the clustered index on a large table.

  • July 6, 2009 at 9:23 pm

    #211827

    I have a large table (with 44 million records) in which the primary key is clustered. Now I would like to make the primary key non-clustered and add a clustered index based on another field.

    Wonder what is the best way to achieve this with minimum or no downtime.

    Any help is appreciated.

    Thanks.

  • July 6, 2009 at 9:32 pm

    #1020846

    Duplicate post, please post replies here.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 6, 2009 at 9:40 pm

    #1020853

    ok, so a lot of rows, but how big is it in MB.. If it is 44M rows of very narrow data then it shouldn't be too bad, if it is a VERY wide table then it will take longer. Also keep in mind that such an operation will grow your transaction log..

    CEWII

  • July 6, 2009 at 9:45 pm

    #1020856

    Its pretty wide table with 10 columns taking about 250 bytes per record.

    (Sorry for duplicate posting, got error messages and after 2 try I realized there were multiple posts)

  • July 6, 2009 at 10:36 pm

    #1020874

    That really isn't that wide, you get about 32-33 records/page, about 1.333-1.375M pages, so about 10GB, that will take a while to re-index.

    Will the new index help with query times? Have you tested this in another environment?

    CEWII

  • July 8, 2009 at 2:31 am

    #1021525

    Hi, Thanks Elliott.

    I just did the following in a test environment (same amount of records).

    Drop the primary key constraint.

    Recreate the (non clustered) primary key.

    Create a clustered index on the field that I wanted.

    The total process takes about 6 minutes with which I can live with. I expected a much longer down time.

    Regards.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply