Views

Overview of Views

Introduction

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.

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.

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.

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:

CREATE VIEW CarIdentification

AS

SELECT Make, Model, CarYear FROM Cars

GO

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.