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
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.
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
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.
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. |
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
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.