NONclustered 2 clustered

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    Comments posted to this topic are about the item NONclustered 2 clustered

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Thanks for this question which seems very simple and evident ( at least it is my own opinion ).

    I am surprised that only half of the voters have found the good choice.

    And thanks also for the link to the sp_help procedure for which I have forgotten the columns returned for indexes.

  • TomThomson

    SSC Guru

    Points: 104763

    patricklambin (4/18/2015)


    Thanks for this question which seems very simple and evident ( at least it is my own opinion ).

    I am surprised that only half of the voters have found the good choice.

    And thanks also for the link to the sp_help procedure for which I have forgotten the columns returned for indexes.

    Well, the numbers were pretty small when you answered - hardly a significant sample.

    Nice simple quesion - but I'm not sure the right answer is evident; yes, it's clear that this is what the engine should do - but that's no guarantee at all that that's what it will do.

    Tom

  • Hany Helmy

    SSChampion

    Points: 13321

    Very nice question, had to test it before answering 😉

    And the shortcut 4 exec sp_help dsa is highlight the table and press Alt + F1

  • karthik babu

    Hall of Fame

    Points: 3312

    Nice Question.. Index remains same irrespective of altering the table by adding a constraint and then by adding an unique clustered index.. I believe the index should have been dropped and recreated as unique clustered primary key in the background. Please correct me if wrong or throw some light on its internal.

    Note: 10 points in 5 days!! 😀

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    karthik babu (4/20/2015)


    Nice Question.. Index remains same irrespective of altering the table by adding a constraint and then by adding an unique clustered index.. I believe the index should have been dropped and recreated as unique clustered primary key in the background. Please correct me if wrong or throw some light on its internal.

    Note: 10 points in 5 days!! 😀

    From BOL:

    If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

  • This was removed by the editor as SPAM

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    What am I missing here? The BOL page for CREATE INDEX [url=https://msdn.microsoft.com/en-us/library/ms188783.aspx]https://msdn.microsoft.com/en-us/library/ms188783.aspx[/url] clearly mentions that:

    The index type cannot be changed by using DROP_EXISTING.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Ed Wagner

    SSC Guru

    Points: 286957

    Thanks, Carlo, for a good question. It gets the brain moving on a Monday.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question. Had to think a bit about possible answer two.

  • Revenant

    SSC-Forever

    Points: 42467

    Nice one, off the beaten path. Thanks, Carlo!

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 12 posts - 1 through 12 (of 12 total)

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