283
Chapter 11: Projecting Data Through Views
11
Any of the following factors may cause a view to be non-updatable:■ Only one table may be updated. If the view includes joins, then the UPDATE state-
ment that references the view must change columns in only one table.
■ Aggregate functions or GROUP BY in the view can cause the view to be non-
updatable. SQL Server couldn’t possibly determine which of the summarized rows
should be updated.■ (^) If the view includes a subquery as a derived table, and any columns from the
subquery are exposed as output from the view, then the view is not updatable.
However, aggregates are permitted in a subquery used as a derived table, so long
as any columns from the aggregate subquery are not in the output columns of the
view.
■ If the view includes the WITH CHECK OPTION, the INSERT or UPDATE operation
must meet the view’s WHERE clause conditions.
Of course, the other standard potential diffi culties with updating and inserting data still apply.
One way to work around non-updatable views is to build an INSTEAD OF trigger that inspects the
modifi ed data and then performs a legal UPDATE operation based on that data. Chapter 18 explains
how to create an INSTEAD OF trigger.
Views and Performance
Views add a level of abstraction that offers an effective method for data access and ad-hoc
queries for end-users, but sometimes views are perceived as having poor performance.
Several factors contribute to this:
■ Views are often used by power users who submit ad hoc SQL. In earlier versions of
SQL Server, ad hoc SQL didn’t perform as well as stored procedures.
■ Views are often used by power users who use front-end UI applications to select and
browse data. Some of these applications opened the connections and held locks,
causing all sorts of performance problems.
■ (^) Views are often used by power users who fi nd useful data in a view and then build
new views on top of views. These nested views might result in a complex view sev-
eral layers deep that kills performance, whereas the top-level view appears to be a
simple, easy view.
Let me put the myth to rest: Well-written views will perform well. The reason to limit
views to ad hoc queries and reports isn’t for performance, but for extensibility and
control.
c11.indd 283c11.indd 283 7/30/2012 4:41:11 PM7/30/2012 4:41:11 PM
http://www.it-ebooks.info