June 22, 2009 at 10:41 pm
Comments posted to this topic are about the item Disabling Indexes
June 23, 2009 at 1:30 am
Does anyone know whether the fact that disabling an index on an indexed view deletes the index data is noted anywhere in books online?
I found the information in Andy Warren's SSC article http://www.sqlservercentral.com/articles/Indexing/63533/, but could not find it in BOL. Intuitively it seems to makes sense (if re-enabling an index always requires you to rebuild it anyway, then it makes sense that the index data could be deleted when you disable), but exactly the same "intuitive" argument seems to apply to non-clustered indexes, and there it does not hold true.
I guess that leads to a follow-up question - does anyone know why SQL server behaves differently for disabling non-clustered indexes on a table, vs indexed views?
Thanks!
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.
June 23, 2009 at 3:29 am
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/1e04f7d6-3d82-45ab-9318-92e0640150b2.htm
June 23, 2009 at 4:05 am
As I understand it disabling the index does not delete the index, it remains in the system catalog but is not used by queries and is not maintained as the data is changed, hence the table becomes inaccessible if you disable a clusterd index.
To enable the index again that is when you have to drop and recreate it (as it is out of date).
So strictly speaking this option is not a correct answer to the question as stated.
---------------------------------------------------------------------
June 23, 2009 at 4:16 am
Apologies, just reread the options properly, they are correct. :blush:
BOL does say indexes are deleted from index views. search on disabled indexes then see 'about disabled indexes'
---------------------------------------------------------------------
June 23, 2009 at 4:16 am
Thanks Toreador, I had not noticed the sentence near the top "Disabling a nonclustered index or clustered index on a view physically deletes the index data."
Do you know why it works this way, though? Why does the index data get deleted for views but not for non-clustered indexes?
George, I'm afraid I don't understand what you mean:
george sibbald (6/23/2009)
So strictly speaking this option is not a correct answer to the question as stated.
Which answer are you referring to?
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.
June 23, 2009 at 4:18 am
george sibbald (6/23/2009)
Apologies, just reread the options properly, they are correct. :blush:
Sorry, I jumped the gun 🙂
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.
June 23, 2009 at 4:23 am
Tao Klerks (6/23/2009)
george sibbald (6/23/2009)
Apologies, just reread the options properly, they are correct. :blush:Sorry, I jumped the gun 🙂
no worries. Exactly what I did when I answered the question. Must. read. options. carefully.
---------------------------------------------------------------------
June 23, 2009 at 5:51 am
Tao Klerks (6/23/2009)
Does anyone know whether the fact that disabling an index on an indexed view deletes the index data is noted anywhere in books online?
It's in the third sentence of the link to SQL Server 2008 Books Online (May 2009)
below:
June 23, 2009 at 2:45 pm
Tao Klerks (6/23/2009)
Why does the index data get deleted for views but not for non-clustered indexes?
Hi Tao, it actually does get deleted for non-clustered indexes as well.
The sentence is a little unclear (or at least can be difficult to grasp).
A better explanation can be found here:
Guidelines for Disabling Indexes(http://technet.microsoft.com/en-us/library/ms177456.aspx)
Best Regards,
Chris Büttner
June 23, 2009 at 4:19 pm
Tao, I have not read this anywhere and its a guess so maybe I am setting myself up for a fall, but -
why does index data get deleted when an index on a view is disabled? Perhaps because a regular view is just a select statement, but if you index the view it is materialised, and only then is data physically stored in the database. So in disabling the index, the view is effectively 'unmaterialised' and therefore to be consistent with view structures the data is deleted.
Just an (educated?) guess, maybe someone else can confirm or otherwise.
---------------------------------------------------------------------
June 24, 2009 at 1:31 am
Some people are wondering why clustered indexes are treated different from non-clustered indexes and indexes on views.
Remember that nonclustered indexes on tables are redundant data. They can be reconstructed anytime from the underlying data. So nothing is really lost when the index data is just droppen when the index is disabled, since re-enabling involves a complete rebuild of the data anyway. (Not dropping it would mean retaining some data that won't be used as long as the index is disabled and that will be replaced when the index is reenabled, so that would just be a waste of disk space).
The same goes for all indexes (both clustered and nonclustered) on views - the clustered index on the view can be reconstructed by processing and materializing the view, and the nonclustered indexes can then be reconstructed jsut as for tables.
Clustered indexes on a base table are different. They ARE the vehivle that stores the data. If disabling the data would delete the data, then the entire contents of the table would be gone. There is nothing to reconstruct the data from, so re-enabling the index would leave you with an empty table. That's why SQL Server simply has to retain the contents of a clustered index on a base table when it is disabbled - because otherwise, disabling the index would just be another way to empty the table (and there are already enough methods for that).
June 24, 2009 at 1:58 am
Christian Buettner (6/23/2009)
Tao Klerks (6/23/2009)
Why does the index data get deleted for views but not for non-clustered indexes?Hi Tao, it actually does get deleted for non-clustered indexes as well.
The sentence is a little unclear (or at least can be difficult to grasp).
A better explanation can be found here:
Guidelines for Disabling Indexes(http://technet.microsoft.com/en-us/library/ms177456.aspx)
Thanks Chris, that makes perfect sense - I just couldn't get that from the first BOL article (or Andy's article that I referenced above) :-). It's nice to find that one's "instinct" regarding SQL server behaviour is not too far from reality!
george sibbald (6/23/2009)
Just an (educated?) guess, maybe someone else can confirm or otherwise.
Thanks George, that was my guess exactly, but what I wasn't understanding was why that exact same argument wouldn't also apply to non-clustered indexes - and as Chris pointed out, it turns out that it does!
Hugo Kornelis (6/24/2009)
Some people are wondering why clustered indexes are treated different from non-clustered indexes and indexes on views.
Thanks for filling in the gaps, Hugo - in my question I should have explained why I expected non-clustered indexes to have disabled non-clustered index data deleted as well...
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.
June 24, 2009 at 8:23 am
Good to know. I never knew this. - Disabling a nonclustered index or clustered index on a view physically deletes the index data.
SQL DBA.
December 11, 2009 at 3:57 am
Well I got this one right
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply