Thursday, 3 May 2012

Altering an indexed view drops the indexes

Whilst altering the an indexed view yesterday I noticed some quirky behaviour that I was not expecting until a colleague of mine kindly pointed it out to me.

If you alter an indexed view, then all the indexes for that view will be dropped too! (This is not pointed out to you in any of the feedback messaging that SQL server outputs either when altering the view).

So you'll have to put back the indexes, with the clustered index being created again first, before you can use the NOEXPAND hint in any dependant database object.

No comments:

Post a Comment