Menu
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.
704
With schemabinding can improve performance in some cases. Edit: Paul White indicated that this doesn't actually impact view table spools just used for joins. Schema binding definitely adds some additional maintenance when changing schema.
SCHEMABINDING. Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
Answer: SCHEMABINDING keywords prevent tables used in the views to make any such modifications that can affect the view's definition. When this keyword is used in the view it binds the view to the schema of the underlying tables.
You Can't… The view is created with the WITH SCHEMABINDING option. You can't always predict what the query optimizer will do. If you're using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used.
You wont be able to alter/drop the table, unless you drop the view first.
74
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.
Good luck with that one!
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.
22
None at all. It's safer. we use it everywhere.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
