Downsides to “WITH SCHEMABINDING” in SQL Server?

问题:

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.

回答1:

None at all. It's safer. we use it everywhere.

回答2:

You wont be able to alter/drop the table, unless you drop the view first.

回答3:

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.

回答4:

If these tables are from a third-party app (they're notorious for trying hide their tables), you cause and upgrade to fail if it attempts to alter any of these tables.

You just have to alter the views without the schemabinding before the update/upgrade and then put them back. Like others have mentioned. Just takes some planning, discipline, etc.

回答5:

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.

回答6:

Another downside is that you need to use schema qualified names for everything: You'll get a load of error messages like this:

Cannot schema bind view 'view' because name 'table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Also to 'switch off' schemabinding you do alter view which requires you to redefine the view's select statement. I think the only thing you dont have to redefine is any grants. This puts me off a lot as overwriting the view seems like an inherently unsafe operation.

Its a bit like the way adding not null constraints forces you to overwrite the column's data type - nasty!

You'll also have to redefine any other views or procedures that depend on the schema bound object you want to change... this means you may have to redefine (and possibly break) a large cascade of functions and views just to add (eg) a not null constraint to one column.

Personally I think this doesnt really represent a solution and its better to have a decent process whereby any database changes are applied automatically so it isnt a nightmare to change the database. That way you can have all your views + functions dropped and recreated from scratch (they get checked on creation anyway) as part of the process when you apply changes to tables.

回答7:

this seems like a downside to me (#'s are mine):

Cannot create index on view "###.dbo.###" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

I kinda need my LEFT joins. This SO question is relevant.

回答8:

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.

回答9:

The negatives mentioned hardly outweigh this best practice since SQL Svr 2005. It avoids the dreaded table spooling. A major negative for me is that schema bound sprocs, funcs, views, can't include "foreign" databases such as the master db, so you can throw all the great realtime system stuff in the trash unless, for example, your production core database sits inside master. For me, I can't deal with life without the sys stuff. Of course not all processing requires spool-free performance and fast and slow results can be combined simultaneously in higher data class layers.

回答10:

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