O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required