Real world View question

N2IT

When you build out Views in your data warehouse or transaction database do you generally use schemabinding?

My understanding is if you bind the view it always updates with the table/tables that it's associated with creating some cost / overhead. This also has to be done if you want to create a index on the view.

My question is really this, why would you want to create a view without schemabinding? I guess in a data warehouse environment when there isn't much change accept adding additional records to the tables takes place.

Thanks in advance.

NotHackingYou

This pretty much falls into 3 categories for me.

1. If the view requires an index, yes
2. If the company I work for designed the database, then generally yes
3. If I'm tacking a view onto a vendor's database, then generally no

There are some other caveats to views which are schemabound. The select satement has to have the two part names schema.tablename (or other object name).

When you create a view with schemabinding you are telling SQL Server not to allow any changes to the underlying object(s) which would break the view. This can change the query plan against the view (generally for the better).

A lot of organizations purchase software from a vendor with a maintenance agreement. The org runs the software locally and maintains it but has to stay 'within warranty' to receive service. When we work on systems like these, we must be very careful not to impose additional restrictions or pressure on the system. Generally, these systems are mission critical systems where downtime of even 1 minute is not acceptable.

These are cases where I would not always use schemabinding. We don't always know all the DDL that each of these upgrade packages will perform. We don't always know all of the ways the software interacts with the database and we don't want to introduce unexpected behavior.

Some systems like these get vendor patches on a regular basis (service packs and hotfixes) as well as annual upgrades (releases). We also must be careful not to break an upgrade package halfway through because it couldn't drop or alter a table that our view depends on. In these cases, I'll prefer to create the view without schemabinding (if appropriate) or I'll just drop the view before the upgrade run and re-create it after. Creating objects like views, indexes, stored procedures, udfs gets a little tricky when you get into this territory.

For us, an appropriate case for a non-schemabound view in this case would be one small enough that it does not require an index and one that is non mission critical.

N2IT

Carl thanks for the follow up. Currently I am crushing through some heavy statistics for this Six Sigma effort that I am rolling into. This has been a lot information thrown my way, hence why I was late following up.

However I am almost done and I will be able to spend 100% of my next week ramping up on SSIS packages.