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 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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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