A query is a technique of
isolating a series of columns and records of a table. This is usually done for
the purpose of data analysis. This can also be done to create a new list of
items for any particular reason. Most of the time, a query is created
temporarily, such as during data analysis while using a table, a form, or a web
page. After using such a temporary list, it is then dismissed. Many database
applications, including Microsoft SQL Server, allow you to create a query and be
able to save it for later use, or even to use it as if it were its own table.
This is the idea behind a view.

View Creation

A view is a list of columns or a series of records retrieved
from one or more existing tables, or as a combination of one or more views and
one or more tables. Based on this, before creating a view, you must first decide
where its columns and records would come from. Obviously the easiest view is one whose
columns and records come from one table.

Fundamentals of Creating Views

The Enterprise Manager

To create a view, you can proceed from the SQL Server
Enterprise manager, The Visual Studio .NET's Server Explorer, or the SQL Query
Analyzer.

To create a view in the Enterprise Manager:

You can right-click the name of the database in the left frame, position the
mouse on New, and click View...

You can first click the database in the left frame. Then, right-click an
empty area in the right frame, position the mouse on New, and click View...

In the left frame, you can right-click the Views node under the name of
the database and click New View...

If you had previously selected the database in the left frame, you can
right-click the Views node in the right frame and click New View...

Any of these actions would display the New View window:

Before creating the view, you would have to specify the
table(s) that would be involved. To do this, you can either click the Add Table
button on the toolbar,
or right-click the top section of the window and click Add Table... This would
display the Add Table dialog box:

To specify the table that would be used as the source, you
can click it in the list box of the Tables property page. If you would be using
another existing view, from the Views property page, you can click the name of
the desired view. If a function would be used to generate the records, you can
locate it in the Functions property page. After selecting the source object, you
can either double-click it or you can click it once and click Add. In the same
way, you can add more than one existing table, view, or function. After
selecting the source(s), you can click Close on the Add Table dialog box. After
selecting the source, it(they) would display the list(s) in the top section of the
New View window:

To select a column, you can click its check box in the top
list. This would display it in the first empty box under the Column column and
would add its name to the SELECT statement. Alternatively, you can click
an empty box in the Column column to reveal its combo box, then click the arrow
of the combo box and select the desired column from the list. Here is an
example:

After selecting the column, its check box would be checked
in the top section of the window, its name would be displayed in the Column
column, and it would be added to the SELECT statement. If you know the
name of the column you want to add, you can manually type it in the SELECT
statement.

To structure of a view can be considered complete when the
SELECT statement is as complete as possible:

At any time, to test the results of a view, you can run it.
To do this, you can click the Run button .
This would cause the bottom section of the view to display the results of the
query. Here is an example:

As stated already, one of the reasons for creating a view,
rather than a classic query, is to be able to use it over and over again. To
achieve this, the view must be saved. Like most objects in SQL Server, a view
must have a named and it is saved in its own file. To save a view from the New
View window, you can click the Save button on the toolbar. You can also attempt
to close the window. You would then be prompted to save it. When saving a view,
you should give it a name that follows the rules and suggestions of SQL.

The Name of a View

In our lessons, here are the rules we will use to name our
views:

A name will start with a letter. Examples are n, act, or Second

After the first letter, the name will have combinations of underscores,
letters, and digits. Examples are n24, act_52_t

A name will not include special characters such as !, @, #, $, %, ^,
&, or *

A name will not have spaces

If the name is a combination of words, each word will start in uppercase.
Examples are DateHired, RealSport, or DriversLicenseNumber

After saving a view, it becomes part of the Views node of
its database.

The Server Explorer

To create a view in the Server Explorer of Microsoft Visual
Studio .NET, you can right-click the Views node of the database and click New
View. You would be presented with the Add Table dialog box. This allows you to
select one or more tables, one or more existing views, and/or one or more
functions. After making the selection(s), click Add. After selecting the
sources, click the Close button. To select or add a column, proceed the same way
we reviewed for the New View window of Enterprise Manager. After creating the
view, you should save it. To do this, you can click File -> Save View on the
main menu or you can click the Save button on the toolbar.

With Transact-SQL

To programmatically create a view, you use the following SQL
syntax:

CREATE VIEW ViewName
AS
SELECT Statement

The creation of a view starts with the CREATE VIEW
expression followed by a name. The name of a view follows the rules
and suggestions we reviewed above. After the name of the view, use the AS
keyword to indicate that you are ready to define the view.

Because a view is primarily a query, it is defined using a SELECT
statement, using the same rules we studied for data analysis. Here is an example
of a view:

As always, the Query Analyzer provides code template you can
use to create a view. To do this, on the main menu, you would click File ->
New... In the New dialog box, you can double-click Create View, make your
choice, and click OK. This creates placeholders in the code. You can simply
replace the undesired sections with appropriate code.

After creating the SQL statement that defines the view, you
must execute the statement. In SQL Query Analyzer, you can do this by pressing
F5. One the statement is executed, its name is automatically added to the Views
node of Enterprise Manager, even if you don't save its code.