Indexed Views with SCHEMABINDING

Question

We have a number of indexed views, which of course require SCHEMABINDING. The base tables supporting these views are involved in replication. Our DBA recently had to re-create the replication and said that all these indexed views gave him grief because he couldn't drop the base tables and re-create them, which I guess is what replication does.

He told me that, in each of the indexed views, he commented out the 'SCHEMABINDING' and saved the view definition. When he did this, what happened to the associated index of each view? Was it deleted? Will I have to go back to each view, include the SCHEMABINDING command and then re-create each associated index?

Answer 1

You cannot have an index on a view without schemabinding, so yes the indexes were removed and you have to add schemabinding back and re-create the indexes. http://msdn.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx

EDIT: @RedFilter, I just did a test and whether you remove schemabinding by ALTERing the view or DROPing and re-creating it, the indexes are removed.