Allow ALTER VIEW to Add or Remove SCHEMABINDING

Allow ALTER VIEW to Add or Remove SCHEMABINDING by JediSQL

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	773019
Opened:	11/30/2012 9:55:06 AM
Access Restriction:	Public

Workaround(s)

view

We work with indexed views quite a bit, and our applications are dependent on them for acceptable performance. However, once an application depends on a database object, it cannot be removed without breaking the application.

With an indexed view, if one needs to modify a base table, one needs to drop the indexes and run an ALTER VIEW statement with the full SELECT statement in the ALTER VIEW statement just to remove the SCHEMABINDING so that the base tables can be worked with.

Having to have each indexed view's full source code on hand just to remove the SCHEMABINDING can be problematic. For example, we have indexed views on replication subscriber tables. We found that it is best to de-index and un-SCHEMABIND the views when we need to reinitialize the subscriptions (even if the table schemas have not changed, we would get time-outs on the bulk loads against the indexes).

It would be nice if we could set up the pre-snapshot script with commands that could drop the view indexes and remove the SCHEMABINDING without having to have the SELECT statements of the VIEWs in the script. Similarly, the post-snapshot script would put back the SCHEMABINDING and the indexes.

We could readily automate a lot of processes that we currently have to do manually if SCHEMABINDING was a switchable property of VIEWs (and probably all other object types that have the SCHEMABINDING option).

Details (expand)

English

SQL Engine

I propose that the ALTER VIEW statement be enhanced to have syntax options embodying the principle illustrated here:

ALTER VIEW [ schema_name . ] view_name SET SCHEMABINDING = { ON | OFF }
[ ; ]

The ability to change a VIEW's SCHEMABINDING without having to have the full definition in scripts would greatly enhance the ease and automation of indexed view management. I hope you find this suggestion useful as well.

Improved Administration

0 attachments

Posted by Microsoft on 2/20/2013 at 12:08 PM

Hi, even for non-clustered indexes, changes may result in recompile thus to avoid errors, we require schemabinding to be enabled. This ensures that changes to the table do not break the view. We will consider trying to improve this experience in the future, but after investigating further this is unfortunately not something we can improve in the near term.

Posted by Microsoft on 2/20/2013 at 12:08 PM

Posted by JediSQL on 1/31/2013 at 6:45 PM

Well, certainly, the database engine should raise an error if you try to "ALTER VIEW [ schema_name . ] view_name SET SCHEMABINDING = OFF" when there is a clustered index defined on the view. I completely understand why SCHEMABINDING is important for indexed views.

The scenario I deal with is that I have some indexed views for performance. It would definately be usefull to drop the index, undo the SCHEMABINDING, make changes, redo the SCHEMABINDING, and then put the indexes back. That way applications always have the view available.

Posted by Microsoft on 1/31/2013 at 3:59 PM

Thanks for your feedback. Unfortunately implementing an option to turn off schemabinding on views would involve some tradeoffs as this implies that indexed views would be dynamically generated at each run (because otherwise the underlying table may have changed) as a result the performance would likely drop to be closer to that of actually querying the table itself. One option to consider is scripting out the view creation so that when the base table needs to be modified the index view can be created fairly seamlessly (you can perhaps even use a trigger to create the view if modifications to the base table are are rare: http://msdn.microsoft.com/en-us/library/ms189799(v=sql.110).aspx).