Post reply

How To Change A Clustered Index To A Non-Clustered Index?

  • September 17, 2004 at 8:08 am

    #162631

    Hi, the title more or less says it all. I issued the following ...

    create nonclustered index jobitems_jobkey on jobitems(jobkey)

    with drop_existing

    and received the following ...

    Server: Msg 1925, Level 16, State 2, Line 1

    Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.

    I have done this using EM but I want to know how to do this using T-SQL. I was hoping that this could be done without having to drop the index in question.

    Also, I want to do the reverse ie., change a non-clustered index to a clustered index in the same table.

    Any advice would be greatly appreciated.

    Thanks.

  • September 17, 2004 at 9:04 am

    #523517

    Clustered index is a constraint and you have to use "alter table drop constraint" command to drop it. See command sample and details from BOL.

    For index creation, check "create index" command which allows you to create both clustered and non-clustered indexes.

  • September 17, 2004 at 9:44 am

    #523527

    What, even when the clustered index is not on the PK ?

  • September 17, 2004 at 9:49 am

    #523530

    You are right. If it is not primary key or unique key, You can simple drop it with "drop index" command.

  • September 17, 2004 at 9:54 am

    #523533

    OK Allen, thanks for the advice ! Its appreciated.

  • September 17, 2004 at 12:29 pm

    #523550

    Incidentally, EM will tell you what T-SQL it is about to use. Make the desired changes to a table from design mode but do not save them yet. Very near the Save toolbar button is a "Save change script" button. Once you click it and get past any applicable warnings (data loss due to type change or whatever) you will be shown the script that you can copy to the clipboard or save to a file. Very useful. You can then cancel out of making the changes if you wish and then schedule the T-SQL to run whenever/however you choose.

  • September 17, 2004 at 12:48 pm

    #523553

    Hey Journeyman, that's an *excellent* tip. I just used it now and ran the script from QA. What a great learning tool that is.

    Thanks for that - I hadn't ever noticed that one!

    Best regards

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

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