Downsides to "WITH SCHEMABINDING" in SQL Server?

views:

149

answers:

+4 Q:

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 A:

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

gbn 2009-11-02 09:30:18

If there are no downsides, and it's safer (which was my initial impression), then why wouldn't people use it? It seems like protecting your views from accidental breakage would be a priority, or like it should be the other way around - WITH is the default, and you have to declare your views WITHOUT if you want that behavior.

rwmnau 2009-11-02 14:07:15

laziness, too much discipline (eg qualified columns etc)

gbn 2009-11-02 14:11:25

Is there a way to make this the default option, or is it always something that needs to be done consciously?

rwmnau 2009-11-02 14:19:45

unfortunately, no...

gbn 2009-11-02 14:55:57

+3 A:

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

Dan S 2009-11-02 14:18:18

This is a big problem in my view especially if you want to modify the database structure without the original DDL statements handy. In these cases you have to attempt to generate complete DDL statements for the Views/Functions with SCHEMABINDING, drop them and then recreate them. Quite a big task to undergo just to change the size of a column.

jpierson 2010-08-19 15:48:38

+1 A:

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.

Jeff O 2009-11-03 03:32:04

I suppose that's true, and much less invasive than dropping the view for the duration of your DDL. I recently had to change the collation on some columns, and just doing an ALTER/Change collation/ALTER would have been much easier than dropping the view and breaking the application while I was working.

rwmnau 2009-11-03 16:30:55

Unfortunately simply removing SCHEMABINDING via an ALTER statement won't work for indexed Views so in these cases I believe the only solution is still to drop and recreate the view.

jpierson 2010-08-19 15:51:35

I just tested doing an ALTER VIEW on my indexed view to see what would happen. I was expecting to see some type of error (typical of SQL Server in a good way) but instead it just deleted my indexes. So beware of using ALTER on a view just to change whether it is schema bound or not without knowing whether it has indexes first.

jpierson 2010-08-19 16:01:14

If you remove schemabinding (you have to use alter which completely rebuilds the view) you can't have an index anyway, so yes, if you add schemabinding back you'll have to recreate the index.

Jeff O 2010-08-19 16:40:29

ansaurus

tags:

views:

answers:

Downsides to "WITH SCHEMABINDING" in SQL Server?

related questions