DDL Statements and Views – Creating a View

In the previous chapters, base tables were used to describe DDL and DML statements. A base table contains data stored on the disk. By contrast, views, by default, do not exist physically—that is, their content is not stored on the disk. (This is not true for so-called indexed views, which are discussed later in this chapter.) Views are database objects that are always derived from one or more base tables (or views) using metadata information. This information (including the name of the view and the way the rows from the base tables are to be retrieved) is the only information concerning views that is physically stored. Thus, views are also called virtual tables.

Creating a View

A view is created using the CREATE VIEW statement. The general form of this statement is

CREATE VIEW view_name [(column_list)]
	[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]
	AS select_statement
	[WITH CHECK OPTION]Code language: CSS (css)

view_name is the name of the defined view. column_list is the list of names to be used for columns in a view. If this optional specification is omitted, column names of the underlying tables are used. select_statement specifies the SELECT statement that retrieves rows and columns from one or more tables (or views). The WITH ENCRYPTION option encrypts the SELECT statement, thus enhancing the security of the database system.

The SCHEMABINDING clause binds the view to the schema of the underlying table. When SCHEMABINDING is specified, database objects referenced in the SELECT statement must include the two-part names in the form owner.db_object, where db_object may be a table, a view, or a user-defined function.

Any attempt to modify the structure of views or tables that are referenced in a view created with this clause fails. You have to drop the view or change it so that it no longer has the SCHEMABINDING clause if you want to apply the ALTER or DROP statement to the referenced objects. (The WITH CHECK OPTION clause is discussed in detail later in this chapter in the section “INSERT Statement and a View.”)

When a view is created with the VIEW_METADATA option, all of its columns (except columns with the TIMESTAMP data type) can be updated if the view has INSERT or UPDATE INSTEAD OF triggers.

Note – The SELECT statement in a view cannot include the ORDER BY clause or INTO option. Additionally, a temporary table cannot be referenced in the query.

Views can be used for different purposes:

• To restrict the use of particular columns and/or rows of tables. Therefore, views can be used for controlling access to a particular part of one or more tables.
• To hide the details of complicated queries. If database applications need queries that involve complicated join operations, the creation of corresponding views can simplify the use of such queries.
• To restrict inserted and updated values to certain ranges.

Example 11.1 shows the creation of a view.

USE sample;
GO
CREATE VIEW v_clerk
	AS SELECT emp_no, project_no, enter_date
		FROM works_on
		WHERE job = 'Clerk';Code language: PHP (php)

The query in Example 11.1 retrieves the rows of the works_on table for which the condition job = ‘Clerk’ evaluates to TRUE. The v_clerk view is defined as the rows and columns returned by this query. Table 11-1 shows the works_on table with the rows that belong to the v_clerk view bolded.

Example 11.1 specifies the selection of rows—that is, it creates a horizontal subset from the base table works_on. It is also possible to create a view that limits the columns as well as the rows to be included in the view. Example 11.2 shows the creation of such a view.

USE sample;
GO
CREATE VIEW v_without_budget
	AS SELECT project_no, project_name
		FROM project;Code language: PHP (php)

The v_without_budget view in Example 11.2 contains all columns of the project table except the budget column.

Table 11-1 The Base Table works_on

As already stated, specifying column names with a view in the general format of the CREATE VIEW statement is optional. On the other hand, there are also two cases in which the explicit specification of column names is required:

• If a column of the view is derived from an expression or an aggregate function
• If two or more columns of the view have the same name in the underlying tables Example 11.3 shows the explicit specification of column names in relation to a view.

USE sample;
GO
CREATE VIEW v_count(project_no, count_project)
	AS SELECT project_no, COUNT(*)
		FROM works_on
		GROUP BY project_no;Code language: PHP (php)

The column names of the v_count view in Example 11.3 must be explicitly specified because the SELECT statement contains the aggregate function COUNT(*), and all columns in a view must be named.

You can avoid the explicit specification of the column list in the CREATE VIEW statement if you use column headers, as in Example 11.4.

USE sample;
GO
CREATE VIEW v_count1
	AS SELECT project_no, COUNT(*) count_project
		FROM works_on
		GROUP BY project_no;Code language: PHP (php)

A view can be derived from another existing view, as shown in Example 11.5.

USE sample;
GO
CREATE VIEW v_project_p2
	AS SELECT emp_no
		FROM v_clerk
		WHERE project_no ='p2';Code language: PHP (php)

The v_project_p2 view in Example 11.5 is derived from the v_clerk view (see Example 11.1). Every query using the v_project_p2 view is converted into the equivalent query on the underlying base table works_on.

You can also create a view using Object Explorer of SQL Server Management Studio. Select the database under which you want to create the view, right-click Views, and choose New View. The corresponding editor appears. Using the editor, you can do the following:

• Select underlying tables and columns from these tables for the view
• Name the view and define conditions in the WHERE clause of the corresponding query