Professional SQL Server™ 2005 Programming

9.11. Summary

Views tend to be either the most overused or most underused tools in most of the databases I've seen. Some people like to use them to abstract seemingly everything (often forgetting that they are adding another layer to the process when they do this). Others just seem to forget that views are even an option. Personally, like most things, I think you should use a view when it's the right tool to use — not before, not after.

Common uses for views include:

• Filtering rows

• Protecting sensitive data

• Reducing database complexity

• Abstracting multiple physical databases into one logical database

Things to remember with views include:

• Stay away from building views based on views — instead, adapt the appropriate query information from the first view into your new view.

• Remember that a view using the WITH CHECK OPTION provides some flexibility that can't be duplicated with a normal CHECK constraint.

• Encrypt views when you don't want others to be able to see your source code — either for commercial products or general security reasons.

• Using an ALTER VIEW completely replaces the existing view other than permissions. This means you must include the WITH ENCRYPTION and WITH CHECK OPTION clauses in the ALTER statement if you want encryption and restrictions to be in effect in the altered view.

• Use sp_helptext to display the supporting code for a view — avoid using the system tables.

• Minimize the use of views for production queries — they add additional overhead and hurt performance.

In ...