SQL Server allows us to decide whether query objects we create referencing tables can be tightly or loosely bound to the table structure. When you create a view or stored procedure you can specify the WITH SCHEMABINDING option to indicate that the view or stored procedure is invalidated if the underlying table structure is modified. In such a situation, the developer will need to drop and recreate the view or stored procedure. It also, inadvertently , serves as a check against 'breaking' changes like dropping columns and changing column data types without regard for dependencies.
Most of the views I see in practice do not specify this option and a reasonable percentage use the "SELECT *" construct to retrieve data from the base tables. This means that if the underlying table(s) change, the developers will be scrambling to either fix bugs or if they are in the know, running sp_refreshview to synchronize the view metadata. One of the practices I preach is the use of WITH SCHEMABINDING at all times. This will consequently force you
- Not to use the "SELECT *" construct in your views at all. This makes total sense to me even without considering some positive performance points you might gain by specifying exactly what you need to retrieve from the database.
- To use two part names for every column and table you reference i.e. table.column or schema.table.
- To carefully consider making 'breaking' changes to your table structure. In any case, good and thorough designing at all tiers of your application should reduce the need for frequent structural changes at the table level. If you must change stuff, script these changes and apply them as part of a bug fix or patch.
- To seriously re-evaluate your usage of nested views. Yes, I think that they are bad.
I have not yet seen any single advantage (apart from excuses) of not tightly binding your references to tables in your database. If the guys at Redmond were listening, I would suggest that all views be implicitly schema bound instead of allowing the T-SQL developer to shoot themselves in the foot. In fact, it sounds better to me to have a WITH NOSCHEMABINDING option just in case some developer out there wanted to do the un-recommended.