Are there reasons to not use SCHEMABINDING on database objects?

Question

Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime.

The benefit is that schema bound objects protects the database from accidental or deliberate changes that could break dependencies later.

The cost of the protection is that maintenance is more complicated because schema bound objects sometimes require altering multiple objects in a particular order to achieve what would otherwise be a simple localized change. This cost is relative since without the schema binding a change could silently break something that requires more maintenance later.

I think the benefit outweighs the cost, especially for established databases that aren't changing much (less changes so less maintenance) or where you don't know the dependencies (less familiar so more risk).

Are there situations where schema binding shouldn't be used?

Answer 1

I don't see any reasons of NOT using SCHEMABINDING on database objects. Its like safe guarding your objects.

Obviously there are pros and cons associated to it.

If you are not going to create Index Views, then you dont need SCHEMABINDING for views.

Basically, SCHEMABINDING

• prevents the views from becoming invalid due to the change in table structure e.g. dropping columns from table.

• makes UDF's deterministic - as described at Improving query plans with the SCHEMABINDING option on T-SQL UDFs

The only downside that I see is, it will make changing the table schema a bit difficult, even if it is just a small change e.g changing a NOT NULL column to allow nulls.