Post reply

Change primary key nonclustered to primary key clustered

  • February 4, 2015 at 10:24 pm

    #294502

    Hi everyone,

    We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing help on this matter?

    Thanks

  • February 5, 2015 at 4:36 am

    #1775637

    You basically have to rebuild the table, twice. Once to remove the old clustered index and once to add the new one.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 5, 2015 at 11:19 am

    #1775815

    There are steps you need, in order:

    1) drop the nonclustered index

    2) drop the existing clustered index

    3) create the new clustered index

    4) add the new nonclustered index, if any.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

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