31 Oct Sql server tutorial 05 Creating SQL Views
Key Skills & Concepts
- Add Views to the Database
- Create Updateable Views
- Drop Views from the Database
As you learned in Chapter 3, persistent base tables store the SQL data in your database.
However, these tables are not always in a useful form if you only want to look at
specific data from one table or data from multiple tables. For this reason, the SQL
standard supports the use of viewed tables, or views. A view is a virtual table whose
definition exists as a schema object. Unlike persistent base tables, there is no data stored in
the view. In fact, the viewed table does not actually exist—only the definition that defines
it exists. It is this definition that allows you to select specific information from one or
more tables, based on the query statements in that definition. Once you create a view, you
simply invoke it by calling its name in a query as you would a base table. The data is then
presented as though you were looking at a base table.
Add Views to the Database
Before I go too deeply into the specifics of views, I want to quickly review some of what I
discussed in Chapters 2 and 3. SQL supports three types of tables: base tables, derived
tables, and transient tables. Of these three types, it is the base tables that hold the actual
SQL data. Most base tables are schema objects and come in five types: persistent base
tables, system-versioned tables, global temporary tables, created local temporary tables,
and declared local temporary tables. Derived tables, on the other hand, are merely the
results you see when you query data from the database. For example, if you request data
from the COMPACT_DISCS table, the results of your request are displayed in a table-like
format, which is known as the derived table. A viewed table is a named derived table
defined by a view definition.
In some ways, a view is like a cross between a persistent base table and a derived table.
It is like a persistent base table in that the view definition is stored as a schema object
using a unique name (within the schema) that can be accessed as you would a base table.
However, a view is more like a derived table in that no data is stored in association with
the view, and is therefore classified as a derived table. Derived tables, including views,
can be thought of as virtual tables. The data is selected from one or more base tables when
you invoke the view. The data results that you see when you access a view are not stored
anywhere but are derived from existing base tables. This is what makes a view a named
derived table, with the view definition stored in the schema.
Views can be useful tools when accessing different types of data. One of the main
advantages of using views is that you can define complex queries and store them within
the view definition. Instead of re-creating those queries every time you need them, you
can simply invoke the view. Moreover, views can be a handy way to present information
to users without providing them with more information than they need or information they
should not see. For example, you might want users in your organization to have access to
certain employee records, but you might not want information such as Social Security
numbers or pay rates available to those users, so you can create a view that provides only
the information that they should see. Views can also be used to synthesize complex
structures and present information in a way that is easier for some users to understand,
which in effect hides the underlying structure and complexity of the database from the
Now that you have an overview of what views are, let’s take a look at a few examples
that illustrate how data is extracted from base tables into the type of derived table that is
presented by a view definition. The first example we’ll look at, shown in Figure 5-1, is
based on the COMPACT_DISC_INVENTORY table, which includes six columns.
Suppose you want to view only the CD_TITLE, COPYRIGHT, and IN_STOCK columns.
You can create a view that extracts these three columns from the table and organizes them
as if the data existed in its own table, as shown in Figure 5-1. The
COMPACT_DISCS_IN_STOCK view contains a query that defines exactly what data
should be returned by the view.
Figure 5-1 The COMPACT_DISCS_IN_STOCK view, based on the
You might have noticed that one of the column names in the view (COMPACT_DISC)
is different from the corresponding column name of the COMPACT_DISC_INVENTORY
table (CD_TITLE), even though the data within the columns is the same. This is because
you can assign names to