Does rebuilding a clustered index effect non clustered index.

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    The line that I am most interested in is:

    "The first few service packs of 2000 had bugs that changed the behavior of rebuilding unique clustered indexes back and forth - this is the source of much of the confusion around this myth."

    I have seen with my own eyes the non-clustered indexes rebuilt during a DBCC DBREINDEX, it said it was rebuilding them. I'm guessing that it was during one of these back and forths that so clearly comes to mind.. And is the source of my confusion, its hard to agree with something when personal experience utterly contradicts what is being said. As for SQL 2005+ I have no issues..

    CEWII

  • stephen.brennan

    Grasshopper

    Points: 12

    Elliott Whitlow wrote:

    The line that I am most interested in is: "The first few service packs of 2000 had bugs that changed the behavior of rebuilding unique clustered indexes back and forth - this is the source of much of the confusion around this myth." I have seen with my own eyes the non-clustered indexes rebuilt during a DBCC DBREINDEX, it said it was rebuilding them. I'm guessing that it was during one of these back and forths that so clearly comes to mind.. And is the source of my confusion, its hard to agree with something when personal experience utterly contradicts what is being said. As for SQL 2005+ I have no issues.. CEWII

     

    To any young/inexperienced DBA's or indeed anyone that may stumble across this post, this is why we do not base our opinions on rare personal experiences caused by bugs in (at time of OP) 11 year old software. Stay up to date with the DBMS that you use and base all your decisions on up to date data.

    And when someone is trying to share their recent experience it take it on board and research their views before disregarding them.

     

  • Jeff Moden

    SSC Guru

    Points: 993883

    When I was a kid and long before the internet was born, my Dad gave me a piece of advice that applies even more today because of the internet.

    "Half of all that is written is wrong and the other half is written in such a fashion that you usually can't tell".

    He's also the inventor of a monopropellent fuel (one that needs no external source of oxygen to burn) that seems to prove that bit of wisdom (whether it was his original thought or not) because he used chemicals that almost every chemistry book ever written say to never mix.  It's also been said that no "effective" monopropellent could be made non-toxic.  He proved that wrong, as well.  If you water-down the fuel, you can brush your teeth with it. 😉

    They also claimed that it couldn't be made safe.  It has passed every test the U.S. Navy threw at it including the "bonfire test" where 3 plastic 50 gallon drums have a rather large bonfire built around it.  When the drums finally melted and the fuel spilled into the fire, it almost put the fire out. 😀

    He also used to say "One good test is worth a thousand expert opinions" (emphasis on the word "good").  IIRC, he attributed that bit of wisdom to Werner von Braun and that holds very true in the world of databases.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 3 posts - 16 through 18 (of 18 total)

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