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 BenefitsViews 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 :
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 GuidelinesViews 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:
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. |