Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 10 -
Managing and Using Views

You'll learn how to create stored SELECT statements. You can use the information referenced by these statements like database tables.
You'll learn to create views that perform relational joins between tables in order to combine information from multiple tables.
You'll learn to add, update, and delete rows in tables by using views.
Learn to create a view that is based on other views rather than tables.
Find out how to edit, list, and delete views through Transact-SQL statements and the SQL Enterprise Manager.

Views are static definitions for the creation of dynamic tables constructed from one or more sets of rows according to predefined selection criteria. Views can be used to define numerous combinations of rows and columns from one or more tables. A defined view of the desired combination of rows and columns uses a simple SELECT statement to specify the rows and columns included in the view.

Understanding Views

Simply put, a view is a sort of bookmark on your table, or a way of returning to a window of the information. This window is actually a set of instructions that tell SQL Server how you want to see the information in your view. This includes the parameters, formatting, and other information SQL Server uses to query the database and retrieve your information.

Technically, a view is a stored definition of a SELECT statement that specifies the rows and columns to be retrieved when the view is later referenced. You can define up to 250 columns of one or more tables in a view. The number of rows that you can define is limited only by the number of rows in the tables referenced.


NOTE: If you're familiar with the Access environment, you'll know views as queries. Queries in Access can, depending on their makeup, be manipulated, changed, or updated or have rows deleted from them, just as you would a table. In fact, you can specify a query nearly anywhere you'd indicate a table.

In SQL Server, the queries are called views, but the balance of the information you already know in working with queries is accurate. You can update views, as they're based on simple or complex SELECT statements or functionality, and your application can refer to them in statements just as it would the underlying tables.


Views are aptly named because they function as the set of rows and columns that you can see through their definition. After the view is defined, you reference the view as if it were a table. Although a view appears as a permanent set of rows and columns stored on a disk in a database, it's not. A view doesn't create a permanent copy of the selected rows and columns of a database.

A view performs the SELECT statement contained within its definition when the view is later referenced just like a table. The temporary table that is created and returned to the monitor is unavailable after the display of its rows is complete. A view enables you to execute a SELECT statement when you reference the view as a table.


CAUTION: It's easy to be misled and believe that a view is a table. After the view is defined, you always access data through it as if it were a table. Try to remember that the data referenced through a view is always coming from its underlying table. Also, if you add columns to the underlying table that the view is defined on, the new columns don't appear in the view unless the view is first deleted and then redefined.

One thing that you can do to help in this area is to use consistent naming conventions. One example of this might be to add a prefix to all views, perhaps starting each with "vw_". This will let you know immediately when you see the name that the underlying object is a view.


A view can be used to access an entire table, part of a table, or a combination of tables. Because the portion of a table you access is defined within the view, you don't have to repeat the selection statements. You can use views to simplify access to the database. Even if you create complicated views that use multiple clauses, you can perform the SELECT statement in the view just as easily as in a view that contains a simple SELECT statement. For more detailed information about creating and working with SELECT statements, see Chapter 7, "Retrieving Data with Transact-SQL."

You can also use views to provide security in the database. You can grant permissions on a view that are different from the permissions granted on the tables the view is based upon. You can provide access to only the rows and columns referenced through a view, but not provide access to all rows and columns directly through the table.


See "SQL Server Security," Chapter 20.

Creating a View

You can create a view either through a command-line ISQL session, an ISQL/w session, or through the SQL Enterprise Manager. A view is stored as a separate object in your database through which you have an alternate way of viewing and, with limitations, modifying a table. You should remember that you can create a view only in the current database. The syntax to create a view in an ISQL or ISQL/w session is as follows:

CREATE VIEW view_name [WITH ENCRYPTION] AS
SELECT statement... 
FROM table_name  | view_name
[WHERE clause] [WITH CHECK OPTION]

You can also create a new view through the SQL Enterprise Manager by performing the following steps:

1. Within the current database, select the Views folder.

2. From the Views folder menu click New View. Figure 10.1 shows the Manage Views dialog box for the current database.

FIG. 10.1
You should replace <VIEW NAME> with a name for your view.

3. Enter the view definition within the Manage Views dialog box. You can also select an existing view and edit it. Figure 10.2 shows a previously designed view.

FIG. 10.2
You can use the View box to select other views for display or subsequent editing.

4. Click the Execute tool button, shown in Figure 10.2, to save the new view definition. You'll receive an error message if your view can't be created. The creation of your view will fail if a view already exists with the same name.

Selective Columns

You can define a view that is made up of some, but not all, of the columns of a table. In the following example, a view is defined as a pseudotable that has two of the three columns of the Employees table:

create view twocolumns as
select name, badge
from employees

When you've defined the view, you can use a SELECT statement to access it just like a table. For example, the view created in the previous example can be referenced in the following manner to display the name and badge for all rows:

select *
from twocolumns

Selective Rows

You can also define a view that references some, but not all, of the rows of a table. In the following example, the sales1 view is defined to contain only the rows in which the department is equal to SALES from the Employees table:

create view sales1 as
select name, department, badge
from employees
where department='SALES'

You can also use one or more Boolean operators in the WHERE clause of a SELECT statement to specify the rows contained in the view. In the following example, a view is defined that contains all columns from the table that are members of the Sales department and that have a badge number greater than 1,000.

create view sales2 as
select name, department, badge
from employees
where department='Sales'
and badge>1000

For more information about Boolean operators, see Chapter 7, "Retrieving Data with Transact-SQL."


TIP: You can use a WHERE clause in the SELECT statement that references a view even though the SELECT statement view within the view definition can include a WHERE clause. The view is treated just like an actual table.

Selective Columns and Rows

You can also define a view that comprises a combination of only some columns and rows of a table. In the following example, the view is defined and provides access to only two of the three columns, and only for the rows that contain the Sales department:

create view twocolumnsales as
select name,badge
from employees
where department='SALES'

You can continue to use a simple SELECT statement to reference the view like a table to retrieve the set of columns and rows defined in the view. For example, to show all rows and columns that are defined in the three previous views, you can use the following three SELECT statements:

select *
from twocolumns
select *
from sales1
select *
from twocolumnsales


TIP: You don't necessarily need to specify all of the columns in the table within the view. You can specify specific columns, calling each as a specific part of the SELECT statement. It's not a requirement to use an asterisk (*) to reference all columns defined in the view.

You can't distinguish a view from a table in the way you use a view. You have to see the view definition to distinguish a view from a table. You can create views for all the combinations of rows and columns that you access together from database tables.


NOTE: You can establish a naming convention for views and tables so that the name of each is self-descriptive as a table or view. For example, the table Employees could be named employees_table, and the view sales could be named sales_view. Remember that you can use up to 30 characters for the name of an object such as a view or table. You can also use a single character within the name of a view or table, such as v for a view and t for a table, if you run short of characters.


CAUTION: If you name views and tables so that each is obviously a table or view, for example, employees_table or sales_view, you may defeat the purpose of a view. A feature of the view is that it is nearly indistinguishable from a table. You work with a view in the same way that you work with a table.
It can be an advantage to permit views and tables to be indistinguishable from one another to database users that needn't perform complicated queries. A complicated query can be defined within the view and the user told to use a simple SELECT statement to access the new "table," which is actually the view.

You can encounter some restrictions when you define a view. You can't define a view on a temporary table. Temporary tables are transitory database structures and exist only until data retrieved from a permanent table is displayed to an output device, such as a monitor.

If you were allowed to define a view based on a temporary table, the data might not be available when you reference it through the view. The temporary table on which the view was defined was automatically deleted.


See "Creating Temporary Tables," Chapter 6.

You also can't define a trigger on a view. A trigger can be defined only on a permanent table. It makes sense to define a trigger only on a table because a table is the permanent underlying source of the data for all views. If you were permitted to define a trigger on a view, SQL Server would still have to reference the underlying table to locate the data specified by the trigger. It's simpler to establish triggers that are based directly on tables.


NOTE: A trigger is a database object that is automatically executed when a table row is inserted, updated, or deleted. It's primarily designed to maintain referential integrity. See Chapter 15, "Creating and Managing Triggers," for more information.

In addition, you can't include ORDER BY in the definition of a view. The rows of a view are unordered like the rows of a database table. If you were permitted to use a SELECT statement that includes an ORDER BY clause in a view, the rows would be ordered and a view would have different characteristics than a database table.

If a view is designed to be used like a permanent table, it must have similar or identical characteristics. You can use an ORDER BY clause when you retrieve rows from a view just as you would retrieve rows from a table. Remember, using views is a two-step process. First, you define the view and save it in the system. This is exactly like creating a table in the system for future use. Second, you select information from the dataset that is created by the view--two steps, two opportunities to manage the return information.

When you work with the view, you can also further restrict the values that are returned. For example, you could have a view that limits the results set of a name and address database to only doctors. When you select from the view, you can further limit the results by indicating that you want only the general practitioners.

Think of views and selecting information from them as coin sorters. You know, those banks that everyone's seen where you drop a coin in the top slot, it rattles around in the bank, and it then comes to rest in the proper location for the denomination in the bank--quarter, dime, nickel, and penny. The bank is your view; it sorts and presents the information.

Now, in this example, you have two options. You can say "give me all the dimes," or you can say "give me all the dimes with a date of 1969." This is the SELECT statement against the view. You're deciding which parts of the information represented by the view you're interested in.


See "Using an ORDER BY Clause," Chapter 7.

You also can't use COMPUTE in a view. COMPUTE creates a virtual column for the actual columns of a table or view.


See "Using a COMPUTE Clause in a SELECT Statement," Chapter 7.

You can't use DISTINCT in the SELECT clause in a view. You can however, use DISTINCT in a SELECT clause of the SELECT statement that references a view to return nonduplicate rows. You could also always ensure the rows retrieved through a view are unique by defining a unique key or index on the underlying table that the view references.


See "Using DISTINCT to Retrieve Unique Column Values," Chapter 7.

In the following example, a view that contains DISTINCT in its SELECT clause can't be successfully defined.

create view departments as
select distinct departments
from employees

Msg 154, Level 15, State 2
A DISTINCT clause is not allowed in a view.

You can't use INTO as part of a SELECT statement within a view. INTO redirects rows into another table rather than to a monitor. In Listing 10.1, a view can't be successfully created because it contains an INTO clause in its SELECT statement.

Listing 10.1 A Failed Attempt to Create a View

sp_help two
Name             Owner               Type
-----------------------------------------------------
two               dbo             user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        Oct 2 1994  1:33PM
Column_name   Type    Length Nulls Default_name   Rule_name
--------------- --------------- ------ ----- ---------------
name        char        25     0     (null)          (null)
badge       int         4      0     (null)          (null)
Object does not have any indexes.
No defined keys for this object.
create view selectinto as
select name,badge
into two
from employees
Msg 154, Level 15, State 3

An INTO clause is not allowed in a view.

Simple and Complex Views

In understanding views, you may find it helpful to further categorize them. Recall that you can define views that access multiple tables as well as individual tables. Simple views are those you define that access any combination of rows and columns from which single tables are called. Complex views are those that provide access to the rows and columns of multiple tables.

The syntax for a complex view uses the same syntax in the SELECT statement that is directly used for the retrieval of rows and columns. Use the following syntax to specify the rows and columns from multiple tables of a database:

CREATE VIEW view_name AS
SELECT column_1,...column_n
FROM table_1,...table_n
WHERE table_key_1=table_key_2
,...AND table_key_1=table_key_n

In the following example, the Name and Department columns are referenced from the Employees table and the Hours_Worked column is selected from the Pays table in the definition of the view. The WHERE clause is used to match the rows from the Employees table with the corresponding rows in the Pays table.

The Badge column is used in each table to match the rows in the same way in which a corresponding column can be used to match rows from a SELECT statement that is used outside a view.

create view combo as
select name,department,hours_worked
from employees,pays
where employees.badge=pays.badge

You access the rows and columns through a complex view the same way that you access rows and columns in a simple view. For example, you can reference the rows and columns defined in the combo view with the following SELECT statement:

select *
from combo


TIP: Rather than require a user to perform a complicated SELECT statement, you can place the complex query within the view and have the user reference the view.

Displaying Views

When you create a view, the definition of a view is stored in the syscomments system table. One way that you can display the stored definition of a view from the syscomments table is by using the sp_helptext stored system procedure.


TIP: You can also use sp_helptext to display the text of a stored procedure, trigger, default, or rule as well as a view. Use sp_help to list the characteristics of a view or other objects.

In Listing 10.2, a simple view is defined that selects all rows of the Sales department. The rows of the Employees table are retrieved through the sales view. The sp_helptext sales procedure is used to display the definition of the view.

Listing 10.2 Creating a Basic View

sp_helptext sales1
text
-------------------
create view sale1 as
select * from employees
where department='Sales'

(1 row(s) affected)

The view definition that is stored in the syscomments table is retrieved by sp_helptext and displays the view definition as the row of a table. The column header is text, the view definition is the row, and the count message specifies the count of the one row retrieved.


TIP: If you find that a view has seemingly vanished when you return to SQL Server at a later date after you've created the view, make sure you're in the right database. Views are, by definition, database specific and won't be visible unless you're using the right database.

You can also display the definition of a view through the SQL Enterprise Manager by performing the following steps:

1. Select the Views folder under the Objects folder in the database in which the view was created. Notice that an icon of a pair of eyeglasses appears to the left of each view to distinguish views from other objects. Figure 10.3 shows the expanded list of views that are currently defined for the pubs database.

FIG. 10.3
You can also double-click the selected view to display it.

2. Click the right mouse button to bring up the Manage Views menu. Open the Manage Views menu and choose Edit or open the Manage menu and choose Views. Figure 10.4 shows the definition of the view titleview displayed through the Enterprise Manager.

FIG. 10.4
You can edit the view displayed in the Manage Views dialog box of the SQL Enterprise Manager.

Editing Views

You must use the SQL Enterprise Manager to edit an existing view. You cannot edit a view from a command-line ISQL or ISQL/w session. You would edit a view in order to change the columns or rows that are referenced by the view. For example, you'll need to add the name of a column that you inadvertently omitted when you originally defined the view.

To edit a view through the SQL Enterprise Manager, perform the following steps.

1. Select the Views folder under the Objects folder in the database in which the view was created.

2. Click the right mouse button to bring up the Manage Views menu and choose Edit or choose Views from the Manage menu or double-click the left mouse button on the selected view.

3. Make your changes after the keywords CREATE VIEW <view name> AS.

4. Click the Execute button, shown in Figure. 10.5, in the Manage Views dialog box.

Figure 10.5 shows the view titleview after it has been changed. The view now shows only titles with a price greater than 50.

FIG. 10.5
You can change the name of the view in the Manage Views dialog box to create a new view.

If you examine the SQL statements within the Manage Views dialog box within which your view is displayed, you'll note a conditional statement that precedes the definition of your view. The conditional statement, which begins with the keyword IF, checks to see if your view is already defined and deletes the view in order to redefine it as if it is a new view.

The deletion of your view is done to satisfy the requirements of SQL Server, which does not permit the direct editing of an existing view. The Manage Views dialog box automatically generates the code to delete the view and re-create it with whatever changes you've made.

You could effectively edit an existing view from ISQL or ISQL/w only by deleting the existing view and creating a new one using the same name as the view that you deleted. You'll find it much easier to change views through the SQL Enterprise Manager.

Adding the WITH ENCRYPTION Clause

You may not want users to be able to display the definition of a view from the syscomments table. If you add WITH ENCRYPTION in the CREATE VIEW statement, you can't subsequently list the definition of the view. In Listing 10.3, a view is created whose definition can't be subsequently displayed with the procedure sp_helptext.

Listing 10.3 Creating Protected Views

create view test_view_encryption with encryption as
select * from company
go
sp_helptext test_view_encryption
go
The object's comments have been encrypted.

You also can't view the definition of an encrypted view from the SQL Enterprise Manager. Figure 10.6 shows the information returned when an encrypted view is displayed.

FIG. 10.6
The owner of an encrypted view can still drop it and create a new view with the name of the dropped view.


CAUTION: A disadvantage of encrypting view definitions is that views can't be re-created when you upgrade your database or SQL Server. During an upgrade, the definitions of a view are used to re-create the view, which can't be done if the view definition is encrypted. You would also be unable to upgrade a database if you delete the view definition stored as a row in the syscomments table.


NOTE: You can also encrypt procedures and triggers. The reason for encryption is security. You can prevent users from displaying the objects, such as tables or views, that an object, such as a view, references. You can use encryption along with object permissions to control access to objects and object definitions.

Displaying View Associations

One way that you can display the tables or views upon which a view is defined is to use the system procedure sp_depends. You may need to display the tables or views that a view references in order to discover and correct problems that you may encounter when you use the view.

In the following example, sp_depends shows that the sales view is defined from the Employees table user.

sp_depends sales
Things the object references in the current database.
object                 type             updated selected
---------------------------------------- ----------------
dbo.employees        user table          no          no
(1 row(s) affected)


NOTE: You can also use sp_depends to display information about tables and views that are dependent upon procedures. sp_depends references the sysdepends system table to locate dependencies. sp_depends shows only references to objects within the current database.

You can also display dependencies through the SQL Enterprise Manager. To display the dependencies of a view, click the right mouse button and choose Dependencies from the menu. For example, in Figure 10.7, the dependencies of the view titleview are displayed in the Object Dependencies dialog box. The Object Dependencies dialog box shows that the view is defined based on the four tables.

FIG. 10.7
The same icons used for tables and views in the Server Manager window of the SQL Enterprise Manager are used in the Type column of the Object Dependencies dialog box.

Creating Views of Views

You can define a view that references a view rather than a table. You can also create a view that references several views or a combination of views and tables. In Listing 10.4, the first view that is created is based on a table. A second view is created that references the first view. Regardless of the number of views defined, they must all eventually reference a table because it is the permanent source of data.

Listing 10.4 Creating "Virtual" Views

create view salesonly as
select name,department,badge
from employees
where department='Sales'
go
This command did not return data, and it did not return any rows
create view salespersons as
select name
from salesonly
This command did not return data, and it did not return any rows

In a continuation of the previous example, Listing 10.5 retrieves rows from the permanent table through a view that was defined on a previously created view. sp_depends is used to confirm that the second view was defined based on the first view.

Listing 10.5 Using "Virtual" Views

select * from salespersons
name
--------------------
Bob Smith
Mary Jones
John Garr
(3 row(s) affected)
sp_depends salespersons
go
Things the object references in the current database.
object               type           updated selected
----------------------------- ---------------- --
dbo.salesonly       view            no      no
(1 row(s) affected)

sp_depends doesn't iterate through views that are defined using other views. If a view references another view rather than a table, sp_depends shows the view rather than the original table. The sp_depends procedure shows only the view or table that the view directly references in the view definition.


CAUTION: Although sp_help shows you the columns included in the rows, it doesn't show you the rows included. Clauses, such as WHERE, aren't displayed by sp_help. You must examine the view definition with sp_helptext.
If you want to see the columns or rows that are included in a view that is defined on one or more views, you'll have to use sp_helptext to display all the view definitions. If a view is defined only on tables, the definition of the view displayed by sp_helptext specifies the rows and columns that are included. It's better to define views directly on tables rather than on other views.

You should use the Object Dependencies dialog box in the SQL Enterprise Manager to display object dependencies. Unlike sp_depends, the listing of object dependencies in the Object Dependencies dialog box shows the multiple level of views and tables that a view is based upon. For example, the Object Dependencies dialog box shows that the view lowtitleview is defined directly based on the view titleview, and indirectly to four tables. In Figure 10.8, this is indicated with the eyeglasses icon in the Type column for sequence number five, and the table icon in the Type column for sequence number four. The sequence numbers are used to illustrate level or depth of objects on which the view is defined.

FIG. 10.8
Object dependencies have levels indicated by the sequence number.

Renaming Columns in Views

You can rename the columns of the base tables in the view. Define the list of alternate column names following the name of the view and preceding the keyword, as in the view definition. Use the following syntax to assign alternate names for the columns referenced in a view:

CREATE VIEW view_name [ (view_column_1,...view_column_n) ] AS
SELECT statement...
FROM table_name or view_name
[WHERE clause]

In Listing 10.6, alternate names for the columns of the Employees table are specified as part of the view definition. A single letter is used for the alternate column name in the view. After the list of column names for the view is defined, the alternate column names appear as new column headers as well as in other clauses, such as a WHERE clause.

Listing 10.6 Creating Alternative Names for Columns

create view view8 (a,b,c)
as
select name,department,badge from employees
(1 row(s) affected)
select * from view8
a                    b                    c
-------------------- -------------------- ----------------
Mary Jones           Sales                5514
Dan Duryea           Shipping             3321
John Garr            Sales                2221
Mark Lenard          Sales                3331
Bob Smith            Sales                1
Minty Moore          Sales                7444

(6 row(s) affected)


NOTE: You don't have to create a view to rename the column of a table during retrieval. Instead, you can rename a column with a SELECT clause outside a view using the following syntax:

SELECT column_name=renamed_name
...

A new name that contains embedded spaces can be enclosed within single quotation marks. The new name isn't permanent. It only applies within the SELECT statement.


Renaming Views

You can use sp_rename to rename a view. The system procedure uses the following syntax:

sp_rename old_name, new_name

Use a comma (,) between the old_name and the new_name to separate the parameters from the procedure name. In Listing 10.7, the sales view is renamed sales2. After the view is renamed, sp_depends shows that the renamed procedure is still based upon the permanent Employees table.

Listing 10.7 Renaming a View

sp_rename sales, sales2
Object name has been changed.
sp_depends sales2
Things the object references in the current database.
object             type             updated selected
----------------- ---------------- ------- --------
dbo.employees      user table          no      no

(1 row(s) affected)

You can also rename a view using the SQL Enterprise Manager. To rename a view through the Enterprise Manager, perform the following steps:

1. Left-click the view to select it.

2. Click the right mouse button. Choose Rename.

3. Enter a new name for the view in the Rename Object dialog box.

4. Click OK.

Figure 10.9 shows the Rename Object dialog box. When the Rename Object dialog box is first brought up, the current name of the view is displayed. Edit the existing name, or delete the old name and enter a new name to rename the view.

FIG. 10.9
The same Rename Object dialog box is used to rename other database objects.


TIP: You can use sp_rename to rename other database objects, including tables, columns, stored procedures, triggers, indexes, defaults, rules, and user-defined datatypes.


CAUTION: Although you can rename views with the sp_rename procedure, SQL Server does not change the name of a table or view in the stored definition of a view in the table syscoments. It warns you of this when you rename a view or similar objects. In Listing 10.8, a warning is displayed when the table Employees is renamed with the procedure sp_rename. The procedure sp_helptext shows that the old name of the table, employees, is retained in the definition of the view based on the renamed table.

Listing 10.8 Renaming a View with Dependencies

sp_rename employees,newname
Warning - Procedures, views or triggers reference this object
and will become invalid.
Object name has been changed.
sp_helptext salesonly
go
text
-----------------------------------------------------
create view salesonly as
select name,department,badge
from employees
where department='Sales'
select * from salesonly
go
name                 department           badge
-------------------- -------------------- -----------
Fred Sanders         SALES                1051
Bob Smith            SALES                1834
Sally Springer       Sales                9998

(3 row(s) affected)


CAUTION: However, both the sp_depends procedure and the Object Dependencies dialog box will display the updated name of renamed tables and views. For example, Figure 10.10 shows the object dependencies for the view salespersons, which references the view salesonly while the view salesonly references the table newname, formerly named employees.

You should try not to rename objects unless it's absolutely necessary.


FIG. 10.10
You can display the dependencies for another view or table by selecting its name in the
Subject Object list box.

Dropping Views

You can use the DROP VIEW command to remove a view from a database. Dropping a view has no effect on the permanent table that the dropped view is based upon. The definition of the view is simply removed from the database. The DROP VIEW syntax is as follows:

DROP VIEW view_name_1, ... view_name_n

You can drop multiple views in a single DROP VIEW by using a list of views separated by commas after the DROP VIEW keywords. The following example drops the sales2 view:

drop view sales2
This command did not return data, and it did not return any rows.

You can also use the SQL Enterprise Manager to drop views by performing the following steps:

1. Left-click the view to select it.

2. Click the right mouse button. Choose Drop.

3. Click the Drop All button.

Figure 10.11 shows the Drop Objects dialog box. The view that was selected in the Server Manager dialog box is automatically selected in the Drop Objects dialog box.

FIG. 10.11
You can use the
Show Dependencies button to display the object dependencies before you drop the view.


CAUTION: If you drop a view in which another view is defined, the second view returns the following error when you reference it, such as in a SELECT statement.

Msg 208, Level 16, State 1
Invalid object name `name_of_dropped_view'.
Msg 4413, Level 16, State 1

View resolution was unsuccessful because the previously mentioned objects, upon which the view directly or indirectly relies, don't currently exist. These objects need to be re-created in order to use the view.

You should consider defining views directly on tables rather than other views. Tables are less likely to be dropped than views because tables are the objects in which rows are actually stored, unlike views, which are simply a different way of looking at the data in a table.


Inserting Rows Through Views

In addition to retrieving rows of data through a view, you can also use the view to add rows to the underlying table on which the view is defined. To easily add a row, reference all table columns in the view. In the example shown next, a new row is added to the permanent Employees table through an INSERT statement that specifies the sales view.

After you've created the view, you reference the view in an INSERT statement to add rows just as if you've referenced a table in the INSERT statement. The rows inserted through the view are added to the underlying table on which the view was defined.

In Listing 10.9, the view definition is first displayed to demonstrate that the view references the underlying table, Employees, and is restricted only to rows that contain the department, Sales. When a new row is inserted through the view, the row is subsequently retrieved from both the view and the table.

Listing 10.9 Showing a View Definition, Then Querying the View

sp_helptext sales
go
text
-------------------
create view sales as
select * from employees
where department='Sales'
go
insert into sales
values (`Mark Lenard','Sales',3331)
select * from sales
where badge=3331
go
name                 department           badge
-------------------- -------------------- -----
Mark Lenard          Sales                3331
(1 row(s) affected)
go
select * from employees
where badge=3331
name                 department           badge
-------------------- -------------------- -----
Mark Lenard          Sales                3331

(1 row(s) affected)

In the previous example, the row that was inserted through the view matched the criteria specified in the WHERE clause of the view, that is, the inserted row contained the department, Sales. Although you may find it odd, SQL Server will permit you to insert a row through a view even though it doesn't match the criteria of WHERE clauses defined within the view.

After a row is inserted through a view that does not match the criteria specified in the WHERE clause of the view, you can't retrieve the row through the view. The criteria for rows defined in WHERE prevents you from retrieving the new row that you've just inserted. For example, in the following INSERT statement, a row is inserted through a view into the Employees table on which the view sales is defined. As you'll recall from the definition of the sales view in the previous example, rows can have only the Sales department.

A subsequent SELECT statement is unable to retrieve the newly inserted row through the view. However, the row was added to the underlying table Employees. A SELECT statement that references the table retrieves the new row that was added through the view. Both examples are shown in Listing 10.10:

Listing 10.10 Testing a View

insert into sales
values (`Fannie Farmer','Logistics',6689)
go
select * from sales
where badge=6689
name                 department           badge
-------------------- -------------------- -----------
(0 row(s) affected)
go
select * from employees
where badge=6689
go
name                 department           badge
-------------------- -------------------- -----------
Fannie Farmer        Logistics            6689

(1 row(s) affected)

You can become confused when you add a row to the underlying table through a view in which the row doesn't match the criteria for inclusion in the view. The row can be inserted through the view, but it cannot be retrieved and subsequently displayed through the same view. The row effectively disappears when retrieved through the view, but it still can be accessed through the table on which the view is based.

Fortunately, you can add the WITH CHECK OPTION clause to your view definition to prevent an operation, such as the insertion of a row through a view, that can't be subsequently displayed through the view.

WITH CHECK OPTION, which is applied to the SELECT statement that is defined within the view, restricts all changes to the data to conform to the row selection criteria defined within the SELECT statement. For example, if a view is defined based on the table, Employees, that contains a WHERE clause that specifies only the department, Sales, only rows that contain the department, Sales, can be inserted in the table, Employees, through the view. WITH CHECK OPTION is illustrated in Listing 10.11:

Listing 10.11 Creating a Restrictive View

create view check_with_check as
select * from company
where department='Sales' with check option
go
This command did not return data, and it did not return any rows
insert into check_with_check
values (`Bob Matilda','Field Service',3325,2)
go
Msg 550, Level 16, State 2
The attempted insert or update failed because the target view
either specifies WITH CHECK OPTION or spans a view which specifies
WITH CHECK OPTION and one or more rows resulting from the operation
did not qualify under the CHECK OPTION constraint.
Command has been aborted.
update check_with_check
set department='Hardware Repair' where department='Field Service'
go
(0 row(s) affected)
delete from check_with_check
where department='Field Service'
go

(0 row(s) affected)


TIP: If you find that after you've added a row to a view the row seems to disappear, you may have added a row that doesn't meet the criteria for the view. Another way this can happen is if the view has changed since you added the row, making the row no longer part of the view's dataset.


TIP: You can prevent the first instance of this by using WITH CHECK OPTION, keeping rows from being inserted through the view that don't match the criteria for the view. Of course, this won't help in the second case where the view was changed after the row was inserted, but it's a good measure nonetheless.

In the previous example, a view was created that included all columns of the underlying table on which it was defined. If one or more columns of the underlying tables aren't present in the view, the missing columns must be defined to allow a NULL or have a default value bound to the missing columns. If not, you can't add the row to the table through the view.

In Listing 10.12, a view is created that includes two columns of the Employees table. The insertion of a row through the view is unsuccessful because the Department column was defined with NOT NULL.

Listing 10.12 A View that Fails Due to Not Null Constraints

create view namebadge as
select name,badge
from employees
go
insert into namebadge
(name,badge)
values (`Russell Stover',8000)
Msg 233, Level 16, State 2
The column department in table employees may not be null.

After a default is defined for the Department column and bound to the column in the Employees table, a new row can be inserted through the namebadge view. The addition of the default for the Department column in the Employees table permits a value to be applied by default when a new row is inserted through the namebadge view. This is shown in Listing 10.13.

Listing 10.13 View Is Successful with a Default Implemented

create default deptdefault
as `Sales'
go
sp_bindefault deptdefault, `employees.department'
Default bound to column.
go
insert into namebadge
(name,badge)
values (`Russell Stover',8000)
(1 row(s) affected)


NOTE: A default can be bound to a user-defined datatype or column of a table. A default can't be bound to the column of a view. However, the defaults bound to table columns that are referenced in a view are applied to the columns if a new row is inserted through the view.

Listing 10.14 shows that when the row is inserted into the underlying Employees table through the namebadge view, successive SELECT statements are used to retrieve the new row through the view and the table:

Listing 10.14 Using a View to Retrieve Inserted Rows

select * from namebadge
where name='Russell Stover'
go
name                 badge
-------------------- -----------
Russell Stover       8000
(1 row(s) affected)
select * from employees
where name='Russell Stover'
go
name                 department           badge
-------------------- -------------------- -----------
Russell Stover       Sales                8000

(1 row(s) affected)


See "Defining Defaults," Chapter 13.
See "Creating Database Tables and Using Datatypes," Chapter 6.

Using Views to Delete Rows

You can delete rows through views even though all columns are not referenced in the view. In Listing 10.15, a row that was previously added to the Employees table through the namebadge view is deleted by using the namebadge view. A subsequent SELECT statement demonstrates that the row is deleted.

Listing 10.15 Confirming Deletion of a Row

delete from namebadge
where name='Russell Stover'
go
(1 row(s) affected)
select * from namebadge
where name='Russell Stover'
go
name                 badge
-------------------- -----------

(0 row(s) affected)

You can't delete a row if the criteria specified in the SELECT clause doesn't include the row specified for deletion. It isn't necessary to add WITH CHECK OPTION to the definition of the view to prevent the deletion of rows that don't match the criteria specified by the WHERE clause of the view. In the following example, one or more rows of the Shipping department is specified for deletion through the sales view. Even if multiple rows were stored in the underlying permanent Employees table upon which sales are based, the rows can't be deleted through the sales view.

delete from sales
where department='Shipping'
go
(0 row(s) affected)

You also can't delete a row from the underlying table of a view if the column that you specify in the WHERE clause of a DELETE statement specifies a column that isn't specified in the view. The following example returns an error because the column specified in the WHERE clause isn't present in the namebadge view used in the DELETE statement:

delete from namebadge
where department='Shipping'
go
Msg 207, Level 16, State 2
Invalid column name `department'.

You can, however, delete the row through a view that was defined with a WHERE clause that specifies criteria that include the row or rows specified in the DELETE statement. You can also delete one or more rows directly through the table upon which the view was defined. In the following example, a row is deleted using a DELETE statement that references the table containing the row:

delete from employees
where department='Shipping'
go
(1 row(s) affected)

Using Views to Update Rows

You can use an UPDATE statement to change one or more columns or rows that are referenced through a view. Any changes that you specify through the view are made to the underlying table in which the view is defined. In Listing 10.16, a single row is updated through the sales view:

Listing 10.16 Updating a View

select * from sales
go
name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514
John Garr            Sales                2221
Mark Lenard          Sales                3331
(4 row(s) affected)
update sales
set badge=0001
where name='Bob Smith'
go
(1 row(s) affected)
select * from sales
where name='Bob Smith'
name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1
(1 row(s) affected)

You can change one or more columns or rows so that they no longer meet the criteria for inclusion in the view. Listing 10.17, a row is updated through a view and a column value is changed so that the row no longer matches the criteria defined in the view:

Listing 10.17 Rows Can Be Inserted that Don't Meet the Criteria for a View and Will Not Show Up in Its Results Set

update sales
set department='Field Service'
where name='Bob Smith'
go
(1 row(s) affected)
select * from sales
where name='Bob Smith'
go
name                 department           badge
-------------------- -------------------- -----------

(0 row(s) affected)

You can also update the underlying table by updating through a view that is defined on a view. In Listing 10.18, the update to the Employees table is performed through a view that is defined using the sales view.

Listing 10.18 You Can Update Against a View, Even if It Works Against Another View

select * from onlyname
name
go
-------------------------
Bob Smith
Fred Sanders
(2 row(s) affected)
update onlyname
set name='Bob Orieda'
where name='Bob Smith'
go
(1 row(s) affected)
select * from onlyname
go
name
-------------------------
Fred Sanders
Bob Orieda
(2 row(s) affected)
select * from employees
where name like `Bob%'
go
name                      department           badge
------------------------- -------------------- ------
Bob Orieda                SALES                1834

(1 row(s) affected)

The updated row that was changed through the onlyname view, which was based on the underlying Employees table through the sales view, is displayed through both the nameonly view and the Employees table. The updated row is also displayed through the sales view. The results are shown in Listing 10.19.

Listing 10.19 The Results

select * from sales
where name like `Bob%'
go
name                      department           badge 
------------------------- -------------------- ------
Bob Orieda                SALES                1834

(1 row(s) affected)

Any changes to the data that you make by updates through views are always reflected in the underlying tables. Views permit you to establish virtual tables with data rows organized like tables, though they are dynamically created as the view is referenced. It's convenient to use views as the only access to data. It is best not to allow tables to be directly referenced.


NOTE: Users of older databases, such as hierarchical or network databases, may remember that their databases could be manipulated only through entities equivalent to views. Network databases had to be indirectly accessed through an entity called a subschema. A subschema functioned like a view.

The usual definition of a subschema is that it serves as the entity through which a programmer or user views the database. You always had to use a subschema to access a network database. Usually, a default subschema was created for a network database that permitted access to the entire database if necessary.


You can update underlying tables through multitable views if the updated columns are part of the same table. Listing 10.20 shows a row that is successfully updated through the multitable combo view.

Listing 10.20 Updating Rows Through Multitable Views

create view combo (a,b,c) as
select name,employees.badge,pays.badge
from employees,pays
where employees.badge=pays.badge
go
This command did not return data, and it did not return any rows
update combo
set a='Jim Walker II'
where b=3211
go
(1 row(s) affected)
select * from combo
where b=3211
go
a                         b           c
------------------------- ----------- -----------
Jim Walker II             3211        3211

(1 row(s) affected)

You can't, however, update the view columns that are used to match rows between the tables because they're part of separate tables. In the next example, the column b and column c views are based on the Badge columns in the Employees and Pays tables. An error is returned that cites an unsuccessful update because the columns are from two tables.

update combo
set c=1111, b=1111
where b=8005
go
Msg 4405, Level 16, State 2
View `combo' is not updatable because the FROM clause names multiple tables.

You can update a value in a single column directly through the view, and you can use a trigger to update the corresponding value in a related table. In Listing 10.21, a trigger has been defined to automatically update the Badge column in the Pays table if the Badge column in the Pays table is changed. When the badge is changed through the b column in the combo view, the trigger automatically activates to change the corresponding value in the Pays table.

Listing 10.21 Updating a Row to Test a Trigger

update combo
set b=9999
where c=4411
go
(1 row(s) affected)
select * from combo
where b=9999
go
a                         b           c
------------------------- ----------- -----------
Sue Sommers               9999        9999

(1 row(s) affected)

Exploring Other View Characteristics

A view remains defined in the database if you drop the table upon which it's based. However, an error is returned when the view is referenced if its underlying table is undefined. If you create a new table with the same name as the one referenced by the view that was dropped, you can again retrieve data from the underlying new table through the view.

Listing 10.22 drops a table upon which a view is based. As shown in the following example, sp_help confirms that the view remains defined even though you have deleted the table upon which it's based. When the view is used in a SELECT statement, an error is returned because the table doesn't exist. After the table is re-created and rows are loaded from an existing table, the view is used to reference rows for the underlying new table.

Listing 10.22 View Objects Are Independent of the Tables on Which They Are Based

drop table employees3
go
This command did not return data, and it did not return any rows
sp_help namebadge3
go
Name               Owner                Type
------------------------------- ----------------
namebadge3          dbo                 view
Data_located_on_segment When_created
----------------------- ---------------------------
not applicable          Oct 2 1994 11:45AM
Column_name Type Length Nulls Default_name Rule_name
--------------- ------------- ----- --------------- -
name       char     25     0     (null)     (null)
badge      int      4      0     (null)     (null)
No defined keys for this object.
select * from namebadge3
go
Msg 208, Level 16, State 1
Invalid object name `employees3'.
Msg 4413, Level 16, State 1
View resolution could not succeed because the previously mentioned
objects, upon which the view directly or indirectly relies, do not
currently exist.  These objects need to be re-created for the view
 to be usable.
create table employees3
(name char(25),department char(20),badge int)
go
This command did not return data, and it did not return any rows
insert into employees3
select * from employees
go
(12 row(s) affected)
select * from namebadge3
where name='Sally Springer'
go
name                      badge
------------------------- -----------
Sally Springer            9998

(1 row(s) affected)

If you use a SELECT clause in a view with an asterisk (*) to specify columns, the new columns added to the table with an ALTER TABLE statement won't be available in the old view. The new table columns are made available only if the view is dropped and re-created.

In Listing 10.23, a view is defined that uses an asterisk (*) in the SELECT clause of a SELECT statement to reference all columns of the Employees table. After an additional column is added to the table with an ALTER TABLE statement, the view doesn't display the NULL entries in the new column that was added to the table. The new Wageclass column is available through the view only after the view is dropped and re-created.

Listing 10.23 Views Are Static in Nature and Take into Account those Columns Available When the View is Initially Created

sp_helptext sales3
go
text
-------------------
create view sales3
as
select * from employees3
where department='SALES'
go
(1 row(s) affected)
select * from sales3
go
name                department       badge
------------------------------------ -----------
Fred Sanders        SALES           1051
Bob Orieda          SALES           1834
(2 row(s) affected)
alter table employees3
add wageclass int null
go
This command did not return data, and it did not return any rows
select * from sales3
go
name               department           badge
------------------------------------ -----------
Fred Sanders         SALES            1051
Bob Orieda           SALES            1834
(2 row(s) affected)
select * from employees3
where department='SALES'
go
------------------------- --------------- -----------
Fred Sanders          SALES           1051     (null)
Bob Orieda            SALES           1834     (null)
(2 row(s) affected)
drop view sales3
go
This command did not return data, and it did not return any rows
create view sales3 as
select * from employees3
where department='SALES'
go
This command did not return data, and it did not return any rows
select * from sales3
go
name             department       badge  wageclass
------------------------ ----------- -----------
Fred Sanders      SALES           1051     (null)
Bob Orieda        SALES           1834     (null)
(2 row(s) affected)

Understanding the Advanced Use of Views

After you've worked through views for a bit, you should consider some additional helpful, though perhaps less obvious, uses for them. Views are a powerful component of SQL Server, and they offer the capability for you to leverage your SQL Server implementation by enhancing everything from the user experience to security on the system.

In the next sections, you'll see how you can implement views to achieve more advanced cuts at your information, and how you can use them to enhance the security of the information on your system.

Security Management with Views

It might not seem like security comes into play too much with views, but indeed it's one of the most frequent uses you'll have for them. Whether your intent is to protect the data from users or users from themselves, you'll do well to look into implementing a solid array of views and supporting functions.

The first approach to SQL Server security is, of course, the setting of permissions at the user and group level. This allows you to lock down specific tables and columns, giving you a great deal of protection for the system. One of the drawbacks to this method is that users accessing your database with other than your expected applications may run into trouble. This results in error messages when you've protected a table's columns.

When you're just starting the information analysis of a table, you'll often find that you issue a SELECT * FROM tablename statement. This statement will typically retrieve all rows and all columns from the table so you can determine how to lay out and request the information you need from the table.

If you issue this type of statement against a table that has permissions denied on a given column or columns, you'll receive an error message and no information will be returned.

SELECT permission denied on column...

This can be frustrating for the users if they receive this message and are not aware of the columns that are in the table. If they don't know the columns, they'll be forced to refer to your documentation, or they'll need to talk with support staff to determine the columns available and the correct statement to retrieve them.

You can avoid all of this by creating views and letting users gain access to information using the views, rather than directly querying the underlying tables. As you've seen throughout this chapter, the views have the added advantage of being able to hide database relationship complexities from the user as well.

When you create a view, and select only the columns that accurately reflect the users privilege level to the information, they will be able to use the SELECT * approach. This will eliminate the error message and will keep your database information most accessible to the user.

You can further lock down the information by mixing a series of views and stored procedures. By creating views as read-only, you can then force users to do updates and new record insertions using stored procedures. Since they're using stored procedures, you can control how records are modified, perform checks on the incoming information, and more.


See Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements," for additional information about creating and managing stored procedures.

For extreme situations, you can actually create stored procedures that act as views on your tables. These stored procedures are simple and take the form of your SELECT statement as the content of the stored procedure. When you do this, you can even remove the SELECT permissions on the table. Users will need only to have execute permissions for the stored procedures. This is the ultimate lock-down approach for your tables, but it doesn't come without a price.

For example, you'll have to create stored procedures to do all operations that relate to the tables in your system. This means even the simple things like standard insert operations and allowable browsing of the tables will have to be programmed. In most installations, this isn't necessary, but there will undoubtedly be extreme cases where you need absolute control.

One drawback to this is that your users will have a much more difficult time just browsing the tables from other applications. For example, from Excel, without some alternative approaches, the user won't be able to use the powerful features that are built into the application that enable browsing of ODBC databases. The same may be true of other third-party applications.


TIP: When you're creating a tightly managed, secure system, if you need to test user rights you can use the SETUSER statement to impersonate another user. By doing so, you can "pretend" you're that user, test access points, and then return to your typical developer role and fine tune the process.
In order to use this statement, you must be the SA or the DBO.
The SETUSER statement has the syntax:

SETUSER username [with noreset]

When you execute this statement, the rights associated with the username you specify will become your rights. You can create objects, test your access levels, and so on. Anything this user can or cannot do, you'll be able to mimic.
The statement remains in effect until you issue a SETUSER statement for either a different user or with no parameters, or until you USE a different database, whichever comes first.


Reality Check

Views are a powerful component available to you for use in many different circumstances. Views are an inexpensive resource as they don't store information other than the statements that control the content represented by the view. At IKON, we use views daily in our applications as a way of removing the requirement to do a join at the application level. Since we can use the view to accomplish the join, it saves coding in the application, and your application can be consistent in how it works with the database tables.

One of the key applications in which we've implemented views includes the timekeeping system. This system uses SQL Server as the backend database engine, and it has both Visual Basic and Access components on the client side. The client applications are used to enter timeslips and query the user's database of past slips. The Access application uses different views in presenting users the information in the system at the administrative level. The view makes it possible to use relatively simple applications on the user side without regard for the underlying relationships between the tables.

Additionally, new views have been created to make it easier to query the database with other applications. From Excel to custom-built Visual Basic applications, the view ensures that the database information is selected in the most expeditious manner and that it uses all available indexes. There are some drawbacks to views; for example, if they contain columns not required for the calling routines, they can bog down the processing unnecessarily.

In one instance, a view was taking a substantial amount of time to produce the desired dataset. When a new view was created with a smaller target dataset, it was found that the original dataset contained several columns that were not needed. By removing the columns, it also removed the requirement for joins to support the columns, speeding up the query and results-return process.

In short, and within reason, don't hesitate to create a new view to support a specific program or query process. The overhead is low, the performance payback is potentially high, and you can always drop the view later without impacting your data.

From Here...

In this chapter you've learned to create virtual tables that use a stored SELECT statement to create a subset of the rows and columns, or a combination of the rows and columns, of one or more database tables. You've also learned to change previously defined views using the SQL Enterprise Manager and display and remove views when they're no longer needed. Finally, you learned to add, update, and delete rows through views, applying the changes to the underlying tables from which the views are defined.

For specific information about the topics mentioned in this chapter, see the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.