Downsides to “WITH SCHEMABINDING” in SQL Server?

Question

I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its "friendly" name (the view "CUSTOMERS" is "SELECT * FROM GG120T", for example). I want to add "WITH SCHEMABINDING" to my views so that I can have some of the advantages associated with it, like being able to index the view, since a handful of views have computed columns that are expensive to compute on the fly.

Are there downsides to SCHEMABINDING these views? I've found some articles that vaguely allude to the downsides, but never go into them in detail. I know that once a view is schemabound, you can't alter anything that would impact the view (for example, a column datatype or collation) without first dropping the view, so that's one, but aside from that? It seems that the ability to index the view itself would far outweigh the downside of planning your schema modifications more carefully.

Answer 1

If your tool (ssms etc.) does not handle schema change failures on the base object well / elegantly you could cause yourself some real chaos. That's what I'm sitting with now, and I do realise that this is a fringe case

Answer 2

One downside is that if you schemabind a view, it can only reference other schemabound views.

I know this because I tried to schemabind a view and was met with an error message telling me it could not be schemabound because one of the other views it references is not also schemabound.

The only consequence of this is that if you suddenly want to update a schemabound view to reference some new or existing view, you might have to schemabind that new or existing view as well. In that case, you won't be able to update the view, and you better hope your database developers know how to work with schemabound views.

Answer 3

When using tSQLt Unit Test Framework you will come across issues and will need workarounds when using FakeTable method, which won't allow you to fake a table that is linked to a view with schemabinding.

Answer 4

Oh, there are DEFINITELY DOWNSIDES to using SCHEMABINDING - these come from fact the SCHEMABINDING, especially when coupled with COMPUTED columns "LOCKS" THE RELATIONSHIPS and makes some "trivial changes" darn near impossible.

1. Create a table.
2. Create a SCHEMABOUND UDF.
3. Create a COMPUTED PERSISTED column that references the UDF.
4. Add an INDEX over said column.
5. Try to update the UDF.

Good luck with that one!

1. The UDF can't be dropped or altered because it is SCHEMABOUND.
2. The COLUMN can't be dropped because it is used in an INDEX.
3. The COLUMN can't be altered because it is COMPUTED.

Well, frak. Really..!?! My day just became a PITA. (Now, tools like ApexSQL Diff can handle this when provided with a modified schema, but the issue is here that I can't even modify the schema to begin with!)

I'm not against SCHEMABINDING, mind (and it's needed for a UDF in this case), but I'm against there not being a way (that I can find) to "temporarily disable" the SCHEMABINDING.