View Concepts

3 4

Now that you have a basic understanding of what views are, let's look at them more closely. In this section, you'll learn about the types of views, the advantages of using views, and the restrictions SQL Server places on the use of a view.

Types of Views

Several types of views can be created, each of which has its advantages in certain situations. Which type of view you create is entirely dependent on what you want to use the view for. You can create views in any of the following forms:

• Subset of table columns A view can consist of one or more columns in a table. Probably the most common type of view, it can be used for data simplification or security.
• Subset of table rows A view can contain as few or as many rows as desired. This type of view is also useful for security.
• Join of two or more tables You can create a view by using a join operation. Complex join operations can be simplified when a view is used.
• Aggregate information You can create a view that contains aggregated data. This type of view is also used to simplify complex operations.

Examples of how to use these types of views are presented in the section "Using T-SQL to Create a View" later in this chapter.

Views can also be used to consolidate partitioned data. Data in a large table can be partitioned across multiple smaller tables for easier management, and then views can be used to merge these tables into a larger virtual table for easier accessibility.

Advantages of Views

One advantage of using views is that they always provide up-to-date data. The SELECT statement that defines a view is executed only when the view is accessed, so all changes to the underlying table are reflected in the view.

Another advantage of using views is that a view can have a different security level than that of the underlying table. The query that defines the view is run under the security level of the user who created the view. Thus, you can use a view to mask off data that you do not want certain classes of users to see. We'll look at an example of this feature in the section "Subset of Columns" later in this chapter.

View Restrictions

SQL Server places a few restrictions on view creation and use. These restrictions include the following:

• Column limitation A view can reference up to 1024 columns in a table. If you need to reference more columns than this, you'll have to use some other method.
• Database limitation A view can be created on a table only in the database the view creator is accessing.
• Security limitation The creator of the view must have access to all of the columns referenced in the view.
• Data integrity rules Any updates, modifications, and so on to the view cannot violate data integrity rules. For example, if a view's underlying table does not allow null values, the view does not allow them either.
• Nested-view levels limitation Views can be built on other views—in other words, you can create a view that accesses other views. Views can be nested to 32 levels.
• SELECT statement limitation A view's SELECT statement cannot contain an ORDER BY, a COMPUTE or COMPUTE BY statement, or the INTO keyword.

NOTE

---

For more information regarding view restrictions, go to the Books Online Index and look up "Creating a View" and then choose "Creating a View" from the Topics Found dialog box.