Microsoft® SQL Server® 2012 Bible

276

Part II: Building Databases and Working with Data

Creating Views with DDL Code

You can manage views using the Query Editor by executing SQL scripts with the Data

Definition Language (DDL) commands: CREATE, ALTER, and DROP. Even though the designer

may seem like an easy tool to use when creating and modifying views, you should consider

using a query editor as the primary tool for tool editing. The basic syntax for creating a

view follows:

CREATE VIEW schemaname.ViewName [(Column aliases)]

AS

SQL Select Statement;

For example, to create the view vEmployeeList in code, the following command would be

executed in a query window:

USE AdventureWorks2008R2

Go

CREATE VIEW dbo.vEmployeeList

AS

SELECT P.BusinessEntityID, P.Title, P.LastName,

P.FirstName, E.JobTitle

FROM Person.Person P

INNER JOIN HumanResources.Employee E

ON P.BusinessEntityID = E.BusinessEntityID

As with creating any object, the CREATE command must be the only command in the batch.

Although I’m generally opposed to Hungarian notation (tblTablename, intIntegerColumn, and so on) for database

objects, I prefer to preface views with a lowercase v or vw, simply to keep them separate in data source listings,

but, to be honest, most database developers do not preface views with a v. You should also avoid using SELECT*

in views. This is because if the number of columns change or the data type or sizes change, using SELECT* could

cause problems for those applications that consume the views.

The view name must be unique in the database. Attempting to create a view with a name

shared by any other object generates an error.

Executing Views

Technically, you cannot execute a view by itself. A view can only patiently wait to be refer-

enced by a SQL query.

A query (SELECT, INSERT, UPDATE, DELETE, or MERGE) can include the view as a data

source, and that query can be executed. As illustrated in Figure 11-2, a view is useful only

as a data source within a query. You can think of a view as nothing more than a place-

holder for a saved SELECT statement.

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

http://www.it-ebooks.info