Microsoft SQL Server 2012 Bible

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