Understanding Views

You can think of a view as a virtual table, similar to an ordinary table and capable of performing tasks like an ordinary table. This virtual information includes all parameters and other SQL Server-specific information used in executing your query.

A view is a stored definition of a SELECT statement that specifies the rows and columns SQL Server retrieves when the view is opened. A view can contain 1024 columns that are extracted from a single table or multiple tables. A view can return an unlimited number of rows, so the number of rows is dependent on the number of rows in the table or tables referenced.

As a matter of fact, views are named "views" because they enable you view the contents of selected columns or rows in a table. They enable you to perform numerous operations that are similar to those you can perform with tables, such as updating, deleting, and querying data, but have some restrictions that tables do not have. These restrictions are explored later in this chapter. Many people mistakenly think that a view actually stores the data. This is not true; the view merely provides access to the data in the underlying tables. The difference between a view and a table can be shown when a column is added to a referenced table: the column does not automatically appear in the view definition until the view is refreshed after it is either altered or re-created. If the referenced table is modified, you can check on the validity of the view by using the sp_refreshview stored procedure. The view is no longer valid if referenced columns no longer exist or have been renamed .

NOTE

Where is the Data? Remember that the data referenced through a view is always coming from its underlying table. The view is actually a SELECT statement against other views or tables.

Information in views can define a filtering strategy. However, it is not a mandatory task. You could have a view that defines a SELECT statement for a whole table. Or, you could vertically filter them by selecting specific columns. You could also horizontally filter them by selecting certain rows. Filtering strategies enable you to increase the level of protection for confidential data.

View access permission can also be granted to specific users throughout your database, which produces a flexible security management plan.

View Benefits

Views provide many benefits and, because of this, are very common throughout an enterprise database environment. The number one reason a view is created is to protect data from inquisitive eyes. This means that the developer has to worry only about allowing access to the view and further restricting the rows that are returned. Views provide many other benefits, which are as follows :

• Make querying easier . Views enable users to execute specific SELECT statements, without requiring the user to provide the entire SELECT statement each time it executes. For example, if the following view exists:

   CREATE VIEW PriceList AS   SELECT TOP 100 PERCENT     ProductID, ProductName, UnitPrice     FROM NorthWind..Products     ORDER BY ProductName 

  Then users can retrieve the data by using

   SELECT * FROM NorthWind..PriceList 

  Without the view, they would have had to type

   SELECT ProductID, ProductName, UnitPrice   FROM NorthWind..Products   ORDER BY ProductName 

• Hide irrelevant data . Views enable you to SELECT only the data that is needed or of interest.

• Enforce security . Users can view only what you let them see. This may be a set of rows or columns or both. This is especially important when sensitive information is involved, such as salary and credit card information. This information can also be blocked by implementing column-level permissions on the table. Views provide a more efficient data retrieval process and easier security management because permissions are checked and maintained at only one level.

• Export data easily . Views can gather data from different views and tables, thus making it easy to export data. This data can be exported using the Bulk Copy Program (BCP) or Data Transformation Services (DTS). Regardless of the tool that you are using, it is easier to create an export statement if you can tell it to take all the data in a specific table. The view can be used to consolidate this data for this purpose.

The greatest benefits that views provide relate to their role as predefined SELECT statements. This enables them to consolidate relevant information into convenient locations. There are some restrictions on what data can be combined in a view, and the following section covers some of those guidelines.

View Creation Guidelines

Views offer some great advantages, but have several restrictions that do not exist in normal SELECT statements. Before examining the view creation process, you should review some facts and restrictions that deal with views. SQL Server will not let you forget these facts when you are creating your views; it throws up an error, usually detailing what you are missing or doing wrong. The most important of these facts are the following:

• A view is nothing more than a SELECT statement that has been given a name and saved in the database. The view does not contain the data, but defines where the data comes from. The view can often be treated like a table.

• Because a view is only a SELECT statement, you can filter the data that is returned to include some or all of a table's rows or columns. You can stick to the important data.

• The view's definition can comprise of rows and columns from one or more tables in the current or other databases.

• A view can contain a maximum of 1024 columns.

• Defaults, triggers, and rules are not allowed to be associated with a view. The only exception to this rule is the new INSTEAD OF trigger.

• A view can use data that may be anywhere (such as another database) as long as the view is created in the current database.

• View names must follow the rules for identifiers.

• Views cannot be created using the ORDER BY clause unless they use the TOP clause (as in TOP 100 PERCENT ).

• Views cannot be created using the COMPUTE BY or SELECT INTO clauses.

• View names must adhere to uniqueness rules. Two users may create a view named MyView because the object owner makes the fully qualified names unique.

• Views cannot be created on temporary tables.

• Temporary views cannot be created.

• Views can be nested up to 32 levels.

NOTE

Indexed Views Indexed views are new to SQL Server 2000 and can significantly speed up the time required to access a view. Indexed views are discussed at a greater depth in Chapter 10, "Boosting Performance with Indexes."

By now you should have a general feel for what a view is. You should also be aware of the benefits derived from and the restrictions that apply to views. With that knowledge, you can begin the next section. The next section looks at creating the view and the syntax required.