Microsoft SQL Server 2012 Bible

Why Use Views?

Although several opinions exist for the use of views, ranging from total abstinence to overuse, the Information Architecture Principle (from Chapter 2, “Data Architecture”) serves as a guide for their most appropriate use. The principle states that “information … must be … made readily available in a usable format for daily operations and analysis by individuals, groups, and processes …”

Presenting data in a more useable format is precisely what views do best.

Based on the premise that views are best used to increase data integrity and for ease of writing ad hoc queries, and not as a central part of a production application, following are some ideas for building ad hoc query views:

• Use views to denormalize or flatten complex joins and hide any surrogate keys used to link data within the database schema. A well-designed view invites the user to get right to the data of interest.
• Save complex aggregate queries as views. Even power users appreciate a well-crafted aggregate query saved as a view.

Best Practice

Views are an important part of the abstraction puzzle; be intentional in their use. Some developers are enamored with views and use them as the primary abstraction layer for their databases. They create layers of nested views or stored procedures that refer to views. This practice serves no valid purpose, creates confusion, and requires needless overhead.

Data within a normalized database is rarely organized in a readily available format. Building ad hoc ...