281
Chapter 11: Projecting Data Through Views
11
■ (^) Views may not contain compute or compute by columns. Instead, use standard
aggregate functions and groupings. (Compute and compute by are obsolete and
are included for backward compatibility only.)
Nesting Views
Because a view is nothing more than a SELECT statement, and a SELECT statement may
reference any data source, views may reference other views. Views referred to by other
views are sometimes called nested views.
The following view uses vEmployeeList and adds a WHERE clause to restrict the results to
the smartest and best-looking employees:
CREATE VIEW dbo.vEmployeeListDBA
AS
SELECT BusinessEntityID, LastName, FirstName, JobTitle
FROM dbo.vEmployeeList AS vE
WHERE JobTitle = 'Database Administrator';
In this example, the view vEmployeeList is nested within vEmployeeListDBA.
Another way to express the relationship is to say that vEmployeeListDBA depends on
vEmployeeList.
You can easily view dependencies from other objects in SQL Server using Object Explorer’s
view context menu ➪ View Dependencies. Figure 11-3 shows the Object Dependencies dialog
for a nested view.
You can see FROM code dependencies using the function sys.dm_sqlreferencing
entities(). For example, the following query would indicate whether any other SQL
Server object referenced vEmployeeList:
SELECT *
FROM sys.dm_sql_referencing_entities
('dbo.vEmployeeList, 'Object')
Best Practice
Although there may be a good reason for nesting views to support power users who build ad hoc
queries, I don’t recommend nesting views as a general practice. They’re just too diffi cult to diagnose
and maintain. I’ve seen development shops that build their production abstraction layer with nested
views several layers deep.
For other options to nesting subselects within outer queries, see Chapter 9, “Merging Data with Joins,
Subqueries, and CTE’s.”
c11.indd 281c11.indd 281 7/30/2012 4:41:10 PM7/30/2012 4:41:10 PM
http://www.it-ebooks.info