Microsoft® SQL Server® 2012 Bible

277

Chapter 11: Projecting Data Through Views

11

FIGURE 11-2

When the query that references a view is submitted to SQL Server, the query parser picks the

query apart and replaces the name of the view with the view’s SELECT statement.

ad hoc Query

VIEW dbo.vEmployeeList

Query Parser

includes the view

into the submitted

query

SELECT BusinessEntityID, LastName,

FirstName, JobTitle

FROM dbo.vEmployeeList;

SELECT BusinessEntityID, LastName,

FirstName, JobTitle

FROM (SELECT P.BusinessEntityID, P.LastName, P.FirstName, E.JobTitle

FROM Person.Person P

INNERJOIN HumanResources.Employee E

ON P.BusinessEntityID = E.BusinessEntityID);

SELECT P.BusinessEntityID, P.LastName,

P.FirstName, E.JobTitle

FROM Person.Person P

INNERJOIN HumanResources.Employee E

ON P.BusinessEntityID = E.BusinessEntityID

Executed Query

The following SELECT statement references the vEmployeeList view:

SELECT BusinessEntityID, LastName, FirstName, JobTitle

FROM dbo.vEmployeeList

ORDER BY BusinessEntityID

Result (abbreviated):

BusinessEntityID LastName FirstName JobTitle

---------------- ------------ ----------- -----------------------

1 Sánchez Ken Chief Executive Officer

2 Duffy Terri Vice President of Engineering

3 Tamburello Roberto Engineering Manager

4 Walters Rob Senior Tool Designer

When views are referenced from ad hoc queries, a WHERE condition is typically added to

fi lter the data from the view:

SELECT BusinessEntityID, LastName, FirstName, JobTitle

c11.indd 277c11.indd 277 7/30/2012 4:41:09 PM7/30/2012 4:41:09 PM

http://www.it-ebooks.info