SQL Server Performance

Enable/Disable Index in SQL 2000?

Discussion in 'General Developer Questions' started by DilliGrg, Oct 16, 2006.

  1. DilliGrg Member

    Hi,
    Is there anyway to enable and disable index in SQL Server 2000? I know we can do this in 2005 easily but have anyone experienced this in 2000 (triggers can be enabled and disabled in 2000)? Any work around besides dropping and recreating the indices in 2000. Thanks in advance for your inputs.


    DilliGrg
  2. satya Moderator

    There is no such facility in SQL 2000 version.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

    May I ask why you want to do so?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. DilliGrg Member

    quote:Originally posted by FrankKalis

    May I ask why you want to do so?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de


    Frank,
    I was thinking to disable the indices in the table and reenable them as soon as the insert/update is done. As I understand, dropping/creating the clustered index takes more time on large amount of data so I was thinking if there is a way around for this like we have for SQL Server 2005.
  5. Roji. P. Thomas New Member

    Even in SQL server 2005, when you disable an index, the index data will be deleted and when you enable it, the index has to be rebuilt. As you are aware, the indices are physical data structures. So you cannot just disable or enable them, just like constraints or triggers.


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  6. DilliGrg Member

    quote:Originally posted by Roji. P. Thomas

    Even in SQL server 2005, when you disable an index, the index data will be deleted and when you enable it, the index has to be rebuilt. As you are aware, the indices are physical data structures. So you cannot just disable or enable them, just like constraints or triggers.


    Roji. P. Thomas
    http://toponewithties.blogspot.com


    Hi Roji,
    Personally, I haven't used this mechanism in SQL Server 2005 but BOL states this:

    Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in metadata and index statistics are kept on nonclustered indexes. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.


    Thanks for the response.
    DilliGrg
  7. Roji. P. Thomas New Member

  8. FrankKalis Moderator

    Hm, seems the discussion has taken place without me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. Adriaan New Member

    Well, the first part of what BOL syas is interesting in itself, but nobody seems to have noticed the second part:

    "Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt."

    From what I understand this means that when you disable the clustered index, the table becomes read-only.

    It isn't very clear though: it says the disabled clustered index must be dropped or rebuilt in order for the data to become updatable again. Is there no way to re-enable a disabled index?
  10. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hm, seems the discussion has taken place without me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />LOL Frank,<br /> It's never too late to reply. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Adriaan,<br /> Yes I am still puzzled by that statement too that's why I have highlighted the statement on my previous response. Also, when you disable the clustered index on a table, you cannot issue any DML statements (based on ms msdn):<br /><br /><i><br />Disabling Clustered Indexes<br /><br />The following additional guidelines apply to disabling clustered indexes: <br /><br />The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index. This means the following: <br />These operations will fail: SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (on the index), and ALTER TABLE statements that modify table columns or constraints.<br />These operations will succeed: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE, and DROP TABLE.<br />Nonclustered indexes cannot be created while the clustered index is disabled.<br />Existing nonclustered indexes and XML indexes associated with the table are automatically disabled and cannot be accessed.<br />All clustered and nonclustered indexes on views that reference the table are disabled. These indexes must be rebuilt just as those on the referenced table. <br /></i><br />
  11. joechang New Member

    the purpose of the index disable was probably not to speed up inserts and updates

    for these things, there is just no substitute for a proper disk config capable of powering through index rebuilds
  12. DilliGrg Member

    quote:Originally posted by joechang

    the purpose of the index disable was probably not to speed up inserts and updates


    Joe,
    I didn't quite understand what you mean by that. Can you elaborate little more on this?

    Thanks
    DilliGrg
  13. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by DilliGrg</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hm, seems the discussion has taken place without me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />LOL Frank,<br /> It's never too late to reply. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Correct. But replying without adding something new and useful to the discussion is just a cheap way to increase post count. Which is what I'm doing with this posting. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  14. satya Moderator

    If you are really looking this in SQL 2000 then there is no way out, as explained it is only valid for SQL 2005 installation.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  15. DilliGrg Member

    Thanks everyone for your valuable inputs. I still need to do some analysis how it works in 2005.
  16. mahendranb New Member

    To enable constraint [in SQLSERVER 2000]

    alter table table_name check constraint constraint_name

    To disable constraint [in SQLSERVER 2000]

    alter table table_name nocheck constraint constraint_name
  17. DilliGrg Member

    quote:Originally posted by mahendranb

    To enable constraint [in SQLSERVER 2000]

    alter table table_name check constraint constraint_name

    To disable constraint [in SQLSERVER 2000]

    alter table table_name nocheck constraint constraint_name


    I wasn't talking about the constraint enable/disable. My question was related to Index.

    Thanks,
    DilliGrg

Share This Page