SQL 2008 Indexes

  • SA-1

    SSCrazy Eights

    Points: 8113

    Comments posted to this topic are about the item SQL 2008 Indexes

  • MarkusB

    SSC-Dedicated

    Points: 37369

    Ok, I got it right and I don't really want to complain about the question. In fact I think it's a pretty good question.

    But what I find strange is that the question is placed under the header SQL 2008 indexes, but the link in the answer refers to BOL for SQL 2005.

    [font="Verdana"]Markus Bohse[/font]

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    I agree, this is a very good question, but there's something I can't figure out or find in a quick search: Why would you ever want to disable a primary key or other clustered index??

    Does anyone know of use cases?

    Thanks,

    Tao

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I don't know, maybe do a manual import and then delete the dupes??? That's not the way I'd do it, but who am I to say something on that subject ;).

  • Cliff Jones

    SSChampion

    Points: 10517

    According to the guidelines, inserts will fail also. I agree, I am not sure why you would ever want to disable the clustered index. I can't come up with a sound reason.

  • Cliff Jones

    SSChampion

    Points: 10517

    I guess it is a way to take the table offline while preparing to do something with it. That's all I can think of.

  • noeld

    SSC Guru

    Points: 96590

    Actually I have had the need for this feature for a long time.

    Suppose you need to prevent changes on ONE table while you are doing some maintenance work in a Db.

    To Set a DB in read-only mode is too drastic, To Setup that table in a separated File and make it read only is inconvenient.

    To setup INSTEAD OF/AFTER TRIGGERS is expensive in Server resources.

    This solution seems cheap and Fast 😉


    * Noel

  • Cliff Jones

    SSChampion

    Points: 10517

    Yes, that makes sense since there is no way to take a table offline like you can in Oracle.

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Thanks Noel, that makes sense - I'm not sure it's very "cheap" because the clustered index gets rebuilt when it gets re-enabled (according to the BOL article), but still seems better than the alternatives!

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.

  • noeld

    SSC Guru

    Points: 96590

    Ninja's_RGR'us (2/3/2009)


    I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.

    True but like with every thing there is an "it depends" 😀

    You will be kicking people out AFTER the "inserted" and "deleted" tables are created on tempdb. It "could" become a PIG!

    In SQL 2000 inserted and deleted tables were built directly from the transaction log ... not any more 😉


    * Noel

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Ah!, learn something new everyday :P.

    That's a good it depends. There would need to have a buttload of traffic on that table to cause tempdb to crash or become a hotspot (unless it's already hot and getting worse because of that).

    P.S. Just remember to deactivate the trigger before doing your big table load... that could really make a hot-spot of the server!

  • TomThomson

    SSC Guru

    Points: 104773

    noeld (2/3/2009)


    Actually I have had the need for this feature for a long time.

    Suppose you need to prevent changes on ONE table while you are doing some maintenance work in a Db.

    To Set a DB in read-only mode is too drastic, To Setup that table in a separated File and make it read only is inconvenient.

    To setup INSTEAD OF/AFTER TRIGGERS is expensive in Server resources.

    This solution seems cheap and Fast 😉

    It seems expensive to me, re-ednabling the clustered index implies rebuilding every index on the table. If the DB design is reasonable, there are a maybe a small number of roles which have permissions on this table - altering those permissions seems pretty cheap and easy (and also provides the option of making the table read only, which is a little less drastic than making it completely inaccessible - particularly if your objective is, as you suggest, just to prevent changes).

    Tom

  • noeld

    SSC Guru

    Points: 96590

    Tom.Thomson (2/3/2009)


    noeld (2/3/2009)


    Actually I have had the need for this feature for a long time.

    Suppose you need to prevent changes on ONE table while you are doing some maintenance work in a Db.

    To Set a DB in read-only mode is too drastic, To Setup that table in a separated File and make it read only is inconvenient.

    To setup INSTEAD OF/AFTER TRIGGERS is expensive in Server resources.

    This solution seems cheap and Fast 😉

    It seems expensive to me, re-ednabling the clustered index implies rebuilding every index on the table. If the DB design is reasonable, there are a maybe a small number of roles which have permissions on this table - altering those permissions seems pretty cheap and easy (and also provides the option of making the table read only, which is a little less drastic than making it completely inaccessible - particularly if your objective is, as you suggest, just to prevent changes).

    That sounds nice when you control the code.


    * Noel

  • noeld

    SSC Guru

    Points: 96590

    Oh And I have seen *MANY* people using simply db_datareader and db_datawriter.

    You could take those permissions away and then the "entire" DB is inaccessible 🙁


    * Noel

Viewing 15 posts - 1 through 15 (of 16 total)

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