Microsoft® SQL Server® 2012 Bible

279

Chapter 11: Projecting Data Through Views

11

A Broader Point of View

The basic mechanics to create a view and select data from the view are straightforward,

but views have their own particular nuances—topics such as sorting data, updating data

through a view, and nesting views several levels deep. This section examines views from a

broader point of view.

Column Aliases

The column aliases option is rarely used. With syntax similar to the column list for a com-

mon table expression, the view’s column list renames every output column just as if every

column had those alias names in the SELECT statement. The view’s column list names

override any column names or column aliases in the view’s SELECT statement.

The following query alters the vEmployeeList view so that the result columns become ID,

Last, First, and Job:

ALTER VIEW dbo.vEmployeeList (

ID, Last, First, Job)

AS

SELECT P.BusinessEntityID,

P.LastName, P.FirstName, E.JobTitle

FROM Person.Person P

INNER JOIN HumanResources.Employee E

ON P.BusinessEntityID = E.BusinessEntityID

GO

SELECT *

FROM dbo.vEmployeeList

ORDER BY ID

Result (abbreviated):

ID Last First Job

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

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

ORDER BY and Views

Views serve as data sources for other queries and do not support sorting the data within

the view. To sort data from a view, include the ORDER BY clause in the query referencing

the view. For example, the following code selects data from the vEmployeeList view and

orders it by LastName, FirstName. The ORDER BY clause is not a part of

vEmployeeList, but it is applied to the view by the executing SQL statement:

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

http://www.it-ebooks.info