Microsoft® SQL Server® 2012 Bible

278

Part II: Building Databases and Working with Data

FROM dbo.vEmployeeList

WHERE JobTitle = 'Database Administrator';

Result:

BusinessEntityID LastName FirstName JobTitle

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

270 Ajenstat Françoi Database Administrator

271 Wilson Dan Database Administrator

Altering and Dropping a View

It’s likely that you need to change the view’s SELECT statement at some point in time.

After you create a view, you can easily edit the SELECT statement by using the ALTER

command. Altering the view changes the saved SELECT statement while keeping any prop-

erties and security settings in place. This is preferable to dropping the view, losing all the

security settings and properties, and then re-creating the view.

The ALTER command supplies a new SELECT statement for the view:

ALTER SchemaName.ViewName

AS

[SELECT Statement];

Management Studio can automatically generate an ALTER statement from an existing view.

In Object Explorer, select the view, and then choose Script View As ➪ Alter To ➪ New Query

Editor Window from the context menu.

If you no longer need the view, you can completely erase it from the database using the

DROP command:

DROP VIEW SchemaName.ViewName;

A view could also be deleted using Management Studio. In the Object Explorer, select the

view, and then choose Delete from the context menu.

Within a script intended to be executed several times, use the following code to drop and

re-create the view:

IF OBJECT_ID('vEmployeeList') IS NOT NULL

DROP VIEW dbo.vEmployeeList

Go

CREATE VIEW SchemaName.ViewName

AS

[SELECT Statement];

Just to reiterate, views don’t contain any data, so there’s no danger that dropping a view

causes any data loss. However, applications, reports, and other objects might depend on the

view, and dropping the view might break something else. For more about viewing depen-

dencies within SQL Server, see the section “Nesting Views” later in this chapter.

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

http://www.it-ebooks.info