Design of Standard SQL Views

In their basic form, views are designed simply as queries enclosed inside a Create View statement:

      CREATE VIEW dbo.vInventory      AS      SELECT dbo.Inventory.Inventoryid, dbo.Equipment.Make, dbo.Equipment.Model,      dbo.Location.Location, dbo.Status.Status, dbo.Contact.FirstName,      dbo. Contact .LastName, dbo. Inventory. Cost, dbo. AcquisitionType. AcquisitionType,      dbo.Location.Address, dbo.Location.City, dbo.Location.ProvinceId,      dbo.Location.Country, dbo.EqType.EqType, dbo.Contact.Phone,      dbo.Contact.Fax, dbo.Contact.Email, dbo.Contact.UserName,      dbo.Inventory.Rent, dbo.Inventory.Eqld, dbo.Inventory.LocationId,      dbo.Inventory.StatusId, dbo.Inventory.OwnerId,      dbo.Inventory.AcquisitionTypeID,      dbo.Contact.OrgUnitId      FROM dbo.EqType      RIGHT OUTER JOIN dbo.Equipment      ON dbo.EqType.EqTypeId = dbo.Equipment.EqTypeId         RIGHT OUTER JOIN dbo.Inventory            INNER JOIN dbo.Status            ON dbo.Inventory.StatusId = dbo.Status.StatusId               LEFT OUTER JOIN dbo.AcquisitionType               ON dbo. Inventory.AcquisitionTypeID =                  dbo. AcquisitionType . AcquisitionTypeId      ON dbo.Equipment.EqId = dbo.Inventory.EqId                LEFT OUTER JOIN dbo.Location                ON dbo.Inventory.LocationId = dbo.Location.LocationId                   LEFT OUTER JOIN dbo. Contact                   ON dbo.Inventory.Ownerld = dbo.Contact.ContactId      SELECT dbo.vInventory.EqId, dbo.vInventory.Make,      dbo.vInventory.Model, dbo.vInventory.Status      FROM dbo.vInventory      WHERE LocationId = 2 

Although data is accessible through a view, it is not stored in the database inside a view. When the view is referenced in a query, SQL Server simply processes the Select statement behind the view and combines the data with the rest of the query:

      EquipmentId Make                  Model              Status      ------------------------------------------------------------      1           Toshiba               Portege 7020CT     Active      6           NEC                   V90                Ordered      5           Bang & Olafson V4000              Active      1           Toshiba               Portege 7020CT     Active     34           Toshiba               Portege 7030CT     Active     (5 row(s) affected) 

A view is often created on a query that joins many tables and contains aggregate functions:

      Create View dbo.vInventoryCost      WITH SCHEMABINDING      as      select ET.EqType, e.Make, e.Model, Sum(Cost) TotalCost, Count)*) [Count]      from dbo.Inventory I      inner join dbo.Equipment e      on i.EqId = e.EqId           inner join dbo.EqType ET           on e.EqTypeId = ET.EqTypeId      where Cost is not null      group by ET.EqType, e.Make, e.Model 

Syntax

Views can be created and edited by simply executing Create View and Alter View statements:

      {CREATE} {ALTER} VIEW view_name [( column [ ,... n ] } ]      [ WITH < view_option > [ ,... n ] ]      AS      select_statement      [ WITH CHECK OPTION ] 

It is not necessary to specify column names in the header of the view. The view just transfers column names from the Select statement if they are uniquely identified. You just need to make sure that column names are not repeated and that all computed columns also have names assigned (for example, you can add aliases to computed columns).

As with stored procedures and functions, views can be encrypted so that nobody can see their source code. You just need to create or alter it using the With Encryption view option.

The With Schemabinding option allows you to schema-bind a view to database objects (such as tables, views, and user-defined functions) that it references. Once the function is schema-bound, it is not possible to make schema changes on the underlying objects. All attempts to drop the objects and alter underlying objects (which would change the object schema) will fail. When this option is used, all objects inside the view must be referenced using two-part names (owner.dbobject).

The View_Metadata option specifies that SQL Server will return information about the view's columns (not base table columns) to client-side APIs. This feature might be useful for making views with triggers that are updateable.

Design View in Enterprise Manager

When a query is complicated, it is sometimes easier to create it in the Query window of Management Studio (see Figure 8-1). Books OnLine sometimes also refer to it as the View Assisted Editor. You can launch it from the context-sensitive menu of the View node in the Object Browser. Before it displays the Query window, Management Studio prompts you for the tables, synonyms, views, and (table) functions that you want to include in your view. It consists of four components: the Diagram pane (for managing tables visually), the Criteria pane (for managing columns and filter criteria), the SQL pane (for editing SQL statements), and the Results pane (for displaying and editing data). This window is used in a manner similar to the Query Design window in Microsoft Access or Visual Studio, so I will not spend more time describing it here. Since a view is only a stored query, after you are done with query design, you can choose Save from the application menu and the Object Browser will prompt you to name the view.

Figure 8-1: View design in the Query window of Management Studio

Security

Typically, a user does not need permissions on underlying base tables and views when the user has permission to access a view. There are two exceptions—SQL Server checks permissions if all underlying tables and views do not belong to the same database, and when base objects belong to different schemas.

Standard SQL Views in Execution Plans

It is a common misconception that SQL Server creates, stores, and reuses an execution plan of a view. The idea behind this wishful thinking is that SQL Server would optimize the execution plan of the view, so when it is referenced from a stored procedure or a query, SQL Server would just include it in the execution plan of the caller. Although this might sound like a good idea (time would be saved since recompilation would not be necessary and since the "optimal" plan will be used), SQL Server has a better solution.

There is another common misconception about view execution. Although I used the phrase "virtual table" to describe views, SQL Server does not execute the view, create a result set in memory, and then process the rest of the query.

When a SQL view is referenced with other tables in a query, SQL Server compiles everything again and creates a new optimal execution plan. Other elements of the query (joins with other tables, additional criteria, and the list of required columns) typically change the nature of the query significantly so that a new execution plan works better than simply reusing the execution plan of a view.

Note 

Execution plans of other types of views are constructed differently, and I will discuss them in the upcoming "Indexed Views in Execution Plans" and "Execution Plans of Distributed Partitioned Views" sections of this chapter.

Limitations of Views

Keep in mind that views have the following limitations:

• A view can contain up to 1,024 columns.

• A view can be based on tables and other views but not on temporary tables or table variables.

• It is possible to have only 32 levels of nested views.

• The Select statement in a view cannot have an Into clause (it cannot create a new table as a side effect).

• A view cannot have Compute and Compute By clauses.

  Only Instead-of triggers can be created on a view (not After triggers).

  An Order By clause can be used in the view only together with a Top clause.

The last statement points to a very cool workaround if you need to order the results in a view—the attempt to create a view using just the Order By clause will result in a syntax error:

      Server: Msg 1033, Level 15, State 1, Procedure v!nventory_Ordered, Line 25      The ORDER BY clause is invalid in views, inline functions, derived tables,      and subqueries, unless TOP is also specified. 

To solve this problem and avoid the error, add Top 100 Percent to it:

      Create VIEW dbo.vInventory_0rdered      AS      SELECT TOP 100 PERCENT      dbo.Inventory.Inventoryid, dbo.Equipment.Make, dbo.Equipment.Model      FROM dbo.Equipment         RIGHT OUTER JOIN dbo.Inventory         ON dbo.Equipment.EqId = dbo.Inventory.EqId      order by dbo.Equipment.Make, dbo.Equipment.Model 

Editing Data Using Views

It is possible to modify data in base tables through a view:

      Update dbo.vInventory      Set Cost = 2000      Where InventoryId = 1234 

SQL Server has to be able to identify rows and fields that clearly need to be modified. The view cannot contain derived columns (columns based on calculated values such as aggregate functions or expressions).

If a view is created using With Check Option, SQL Server does not accept changes on records that will fall out of scope of the view after modification. For example, a manager who can see Inventory for his own location cannot assign it to some other location:

      CREATE VIEW vInventoryTrigonTower      AS      SELECT *      FROM dbo.vInventory      WHERE LocationId = 2      WITH CHECK OPTION      GO      update dbo.vInventoryTrigonTower      set LocationId = 10      where InventoryId = 6 

SQL Server will generate an error:

      Server: Msg 550, Level 16, State 1, Line 1      The attempted insert or update failed because the target view      either specifies WITH CHECK OPTION or spans a view that specifies      WITH CHECK OPTION and one or more rows resulting from the operation      did not qualify under the CHECK OPTION constraint.      The statement has been terminated. 

With standard SQL views, it is not possible to modify data in more than a single base table. However, when an Instead-of trigger is placed on a view, the trigger can issue separate statements that modify individual base tables. See "Triggers on Views" in Chapter 9 for a detailed example and discussion of this method. It is very interesting and useful.