How does Index Enable/Disable work? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How does Index Enable/Disable work?

Hi,<br /> Have anyone used Index Enable/Disable function in 2005? Can we just reenable the disabled index after the DML statements are issued without dropping/recreating? Hopefully, this won’t fall into ‘duplicate post’ category because I couldn’t get the answer from 2000 forum(I should say I asked in wrong forum, should be here [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]). Thanks in advance for your inputs.<br /><br />BOL says:<br /><i><br />Disabling a nonclustered index or clustered index on a view physically deletes the index data. <br />Disabling a clustered index prevents access to the data, <br />but the data remains unmaintained in the B-tree until the index is dropped or rebuilt<br /></i><br /><br /><br />DilliGrg
Hi, You can not Enable a Disabled Index with out rebuilting it or recreating it. Logically, it is not possible also. Suppose, you have disabled the index on a table and after that lot of data modification occured in the table (ie insertion/deletion). How you disabled index will know these data modification ? When you disable a index the index definition is still there in the meta data. But further index table(index is nothing but a auxilary table) updation will not occur. So when u want to enable the index again , it should be rebuilt or recreated. Madhu
Disabling an index stops that index to be being maintained and used by SQL Server engine. When an index is disabled, SQL Server 2005 database engine de-allocates the storage space used by an index but maintains the meta-data of that index. Alter Index statement is used to rebuild an index and only after that, disabled index can be enabled again.
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.

First of all, thanks for the responses. So, what is the point (benefit) of index enable/disable feature in 2005? I can’t see any benefits of having this feature in 2005. Thanks,
DilliGrg
…SQL engine de-allocates the storage space used by an index but maintains the meta-data of that index 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.
quote:Originally posted by satya …SQL engine de-allocates the storage space used by an index but maintains the meta-data of that index 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.

Thanks Satya. That make sense. Thanks,
DilliGrg
]]>