Chapter 09 - Managing and Using Views
- How to create a virtual table - In this chapter you'll learn to create stored SELECT statements that you subsequently use like database tables.
- How to create a views that are defined on multiple tables - You'll learn to create views that perform relational joins in order to combine information from multiple tables.
- How manipulate data in views - You'll learn to add, update and delete rows through views and their underlying tables.
- How to create a view of a view - You'll also learn to create a view that is based on views rather than tables.
- How to manage views - You'll also learn to edit, list, and delete views through Transact-SQL statments and the Enterprise Manager.
Views are static definitions for the creation of dynamic tables constructed from one or more sets of rows according to a 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
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.
Views are aptly named because they function as the set of rows and columns that you can see through their definition. Once the view is defined, you reference the view as if it is 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 like a table. The temporary table that is created and returned to the monitor is unavailable once the display of its rows is complete. A view enables you to execute a SELECT statement when you reference the view as a table.
It's easy to be misled and believe that a view is a table. Once the
view is defined, you always access data through it as if it's 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 redefined.
A view can be used to access all of a 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. If you create even complicated views that use multiple clauses, you can perform the SELECT statement in the view just as easily as a view that contains a simple SELECT statement.
You can also use views to provide security in the database. You can grant permissions on a view that can be different than 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 the Chapter 19 section
entitled "SQL Server Security"
Creating a View
You can create a view either through a command line isql session, a 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 only create a view 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:
- Within the current database select the Views folder.
- From the View Folder menu click New View. Figure 9.1 shows the Manage Views dialog box for the current database.
- Enter the view definition within the Manage Views dialog box. (You can also select an existing view and edit it.) Figure 9.2 shows a previously designed view.
- Click the Execute tool button (refer to fig. 9.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.
Fig. 9.1 - Replace <VIEW NAME> with a name for your view.
Fig. 9.2 - You can use the View list box to select other views for display or subsequent editing.
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 pseudo-table that has two of the three columns of the Employees table:
create view twocolumns as select name, badge from employees
Once you've defined the view, you can use a SELECT statement to access it just like a table. For example, the twocolumns 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 through the view. In the following example, a view is defined that contains all columns from the table the 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
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.
See the section entitled Creating a View earlier in this chapter.
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 twocolumnsales view is defined and provides access to only two of the three columns, but 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
You can specify only some of the columns defined in the view within
a SELECT clause of a SELECT statement. You don't need
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.
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 instead 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 a v
for a view and a t for a table, if you run short of characters.
If you define 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 that is based on a temporary table, the data may not be available when you reference it through the view. The temporary table on which the view was defined was automatically deleted.
See the Chapter 5 section
entitled Creating
Temporary Tables>Creating Temporary Tables
You also can't define a trigger (see following Note) on a view; a trigger can only be defined on a permanent table. It makes sense to only define a trigger 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.
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 14, "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 like you would if you retrieve rows from a table.
See the Chapter 6 section
entitled Using
an ORDER BY Clause
You also can't use COMPUTE in a view. COMPUTE creates a virtual column for the actual columns of a table or view.
See the Chapter 6 section
entitled Using
a COMPUTE Clause in a SELECT Statement
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 non-duplicate 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 the Chapter 6 section
entitled Using
DISTINCT to Retrieve Unique Column Values
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 the following example, a view can't be successfully created because it contains an INTO clause in its SELECT statement.
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
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.
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 the following example, 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.
sp_helptext sales1 text ------------------- create view sale1s 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.
I used sp_helptext to list the definition of a view that I
defined the last time I used Microsoft SQL Server. Now when I use the procedure,
SQL Server can't seem to find my view. Why?
You're probably pointing to the wrong database. Microsoft SQL Server
maintains separate system table syscomments for each database (along with
12 other system tables). You should first enter the USE command
followed by the name of your database in which you created the view. If
you have selected the correct database, the view should be shown when you
use sp_helptext again.
You can also display the definition of a view through the SQL Enterprise Manager by performing the following steps:
- 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 9.3 shows the expanded list of views that are currently defined for the pubs database.
- 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 9.4 shows the definition of the view titleview displayed through the Enterprise Manager.
Fig. 9.3 - You can also double-click the selected view to display it.
Fig. 9.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'd 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.
- Select the Views folder under the Objects folder in the database in which the view was created.
- Click the right mouse button to bring up the Manage Views menu and choose Edit from the Manage Views menu or Choose Views from the Manage menu or double-click the left mouse button on the selected view.
- Make your changes after the keywords CREATE VIEW <view name> AS.
- Click the Execute button (refer to fig. 9.2) in the Manage Views dialog box.
Figure 9.5 shows the view titleview after it has been changed. The view now shows only titles with a price greater than fifty.
Fig. 9.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 Microsoft SQL Server, which does not permit the direct editing of an existing view. The Manager Views dialog box automatically generates the code to delete the view and recreate 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
A GO command is used after the conditional statement and the
CREATE VIEW statement. GO is a terminator that tells
SQL Server to execute the previous statement.
You may not want users to be able to display the definition of a view from the syscomments table. If you add the WITH ENCRYPTION in the CREATE VIEW statement, you can't subsequently list the definition of the view. In the following example, a view is created whose definition can't be subsequently displayed with the procedure sp_helptext.
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 9.6 shows the information returned when an encrypted view is displayed.
Fig. 9.6 - The owner of an encrypted view can still drop it and create a new view with the name of the dropped view.
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 recreate 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.
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 to prevent
them directly accessing the objects. 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)
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 9.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. 9.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 the following example, the first view that is created is based on a table. A second view is created that references the first view. However many views are defined, they must all eventually reference a table because it is the permanent source of data.
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, the following example 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.
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 iteratively transalte 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.
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 of 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 on only tables,
the definition of the view displayed by sp_helptext specifies
the rows and columns that are included. It's better to directly define
views on tables rather than on other views.
You should use the Object Dependencies in the SQL Enterprise Manager to display object dependencies. Unlike sp_depends, the listing of object dependencies in the Object Dependencies shows the multiple level of views and tables that a view is based upon. For example, in figure 9.8, the Object Dependencies dialog box shows that the view lowtitleview is defined directly based on the view (the eyeglasses icon in the type column) titleview (sequence number five) and indirectly to four tables (the table icon in the type column, sequence number four). The sequence numbers are used to illustrate level or depth of objects that the view is defined on.
Fig. 9.8 - Object dependencies have levels indicated by the sequence number.
Renaming Columns in Views
You can also 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 the following example, 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. Once 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.
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)
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 of 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 the following example, the sales view is renamed sales2. Once the view is renamed, sp_depends shows that the renamed procedure is still based upon the permanent Employees table.
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:
- Left-click the view to select it.
- Click the right mouse button. Choose Rename.
- Enter a new name for the view in the Rename Objects dialog box.
- Click OK.
Figure 9.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. 9.9 - The same Rename Object dialog box is used to rename other database objects.
You can use sp_rename to rename other database objects, including
tables, columns, stored procedures, triggers, indexes, defaults, rules,
and user-defined datatypes.
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 the following example, 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.
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)
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 9.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. 9.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:
- Left-click the view to select it.
- Click the right mouse button. Choose Drop.
- Click the Drop All button.
Figure 9.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. 9.11 - You can use the Show Dependencies button to display the object dependencies before you drop the view.
If you drop a view in which another view is defined, the second view
returns the following error when you reference it (for example, 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 following example, a new row is added to the permanent Employees table through an INSERT statement that specifies the sales view.
Once 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 that the view was defined on.
In the following example, 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.
Once a new row is inserted through the view, the row is subsequently retrieved from both the view and the table.
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; 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.
Once 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 the 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 here:
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 (refer to previous example), 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.
The 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 the following example:
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)
I added a row to a view, but I can't seem to retrieve it when I
reference the row in a subsequent SELECT statement. Why can't
I retrieve the row through the view?
You may have originally added a row that can't be retrieved because of the criteria specified in the view definition. (This is the aforementioned problem involved with disappearing rows.) Or the view may have been subsequently edited so that the row no longer meets the criteria for inclusion in the view.
Or the row may have been deleted through the view, another view, or
directly from the underlying table. You can disable the disappearing row
feature through the use of the WITH CHECK OPTION in the definition
of the view.
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. Otherwise, you can't add the row to the table through the view.
In the following example, 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.
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.
Once 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. An example is shown in the following example:
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)
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.
The following example shows that once 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:
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 the Chapter 11 section
entitled Defining
Defaults>Defining Defaults for more information.
See the Chapter 5 section
entitled Defining
User-Defined Datatypes for more information.
Using Views to Delete Rows
You can delete rows through views even though all columns are not referenced in the view. In the following example, 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.
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 the 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 is 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 a criteria that includes the row or rows specified in the DELETE statement. You can also delete one or more rows directly through the table in which the view was defined. In the following example, a row is deleted by 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. You can change one or more columns of the view. Any changes that you specify through the view are made to the underlying table in which the view is defined. In the following example, a single row is updated through the sales 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. In the following example, 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:
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 the following example, the update to the Employees table is performed through a view that is defined using the sales 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 name 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. Here are 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; don't allow tables to be directly referenced.
Users of older databases, such as hierarchical or network databases,
may remember that their databases could only be manipulated 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 multi-table views if the updated columns are part of the same table. The following example shows a row is successfully updated through the multi-table combo view.
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 the following example, 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.
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.
The following example 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. Once 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.
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 the following example, 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.
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)
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, 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 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 information about selected aspects of the topics mentioned in this chapter, see the following chapters:
- Chapter 5, "Creating Database Tables and Using Datatypes" discusses the treatment of NULLs as column values, as well as the column datatypes.
- Chapter 7, "Performing Operations on Tables" teaches you how to combine rows from multiple tables in the same query, including the use of COMPUTE and COMPUTE BY.
- Chapter 10, "Managing and Using Indexes and Keys" teaches you how to create and use keys and indexes and how to constrain rows and columns to unique values.
- Chapter 14, "Creating and Managing Triggers" teaches you how to create and use triggers to maintain referential integrity in the database.
QUE Home Page
For technical support For our books And software contact support@mcp.com
Copyright © 1996, Que Corporation
