Indexed Views Using SCHEMABINDING

Question

We have a number of indexed views, which of course require DIAGRAMS. Base tables supporting these views are involved in replication. Recently, our database administrator had to recreate replication and said that all of these indexed views gave him sadness because he could not abandon the underlying tables and recreate them, which I think is what replication does.

He told me that in each indexed view, he commented on the “SCHEME” and retained the definition of the species. When he did this, what happened to the corresponding index of each point of view? Has it been deleted? Should I go back to each view, include the SCHEMABINDING command and then recreate each linked index?

Answer 1

You cannot have a pointer to a view without being bound to a schema, so yes, the indexes have been removed, and you need to add the schema back and recreate the indexes. http://msdn.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx

EDIT: @RedFilter, I just ran the test and whether you removed the scrambling using ALTERing view or DROPing and recreated it, the indexes are removed.

Answer 2

Yes, you need to go back to each view, enable the SCHEMABINDING command, and then re-create each linked index.

IIRC, indexes still exist, but are not used. That way, you can simply right-click them to create a DDL to change the index and execute it. It can be easily written for automation.