Views

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.

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.

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.

Using a View

The Enterprise Manager

A view is a type of intersection between a table and a query. Based on this, to view the results of a view, you proceed with the same approach you would use for a table.

In the SQL Server Enterprise Manager, to see the results of a view, in the right frame, you can right-click the name of a view, position the mouse on Open View, and click Return All Rows.

Transact-SQL

To view the result of a view using SQL code, you create a SELECT statement and apply it on the name of the view the same way you would proceed from a table. Here is an example:

The Server Explorer and Windows Applications

In the Visual Studio.NET Server Explorer, if you double-click the name of a view under the Views node, its results would display on the right side of the window.

Since a view is considered a type of table, you can use it in a Windows Application the same way you would use a table as we have done in previous lessons.

Techniques of Creating Views

A View With Arguments

It is important to know that a view is more of a table type than any other object. This means that a view is not a function but it can use a function. The word argument here only means that some values can be passed to a view but these values can be specified only when creating the view. They are not real arguments.

When creating a view, you can create placeholders for columns and pass them in the parentheses of the view. This would be done as follows:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
. . .

If you use this technique, the names passed in the parentheses of the view are the captions that would be displayed in place of the columns of the view. This technique allows you to specify the strings of your choice for the columns. If you want a column header to display the actual name of the column, write it the same. Otherwise, you can use any string you want for the column. If the name is in one word, you can just type it. If the name includes various words, include them between an opening square bracket "[" and a closing square bracket "]".

After listing the necessary strings as the captions of columns, in your SELECT statement of the view, you must use the exact same number of columns as the number of arguments of the view. In fact, each column of your SELECT statement should correspond to an argument of the same order.

Here is an example:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
AS
SELECT TagNumber, Make, Model, Available FROM Cars
GO

Because, as we stated already, a view is not a function and the values passed to the view are not real arguments, when executing the view, don't specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source. Here is an example:

Views and Conditional Statements

Besides its querying characteristics that allow it to perform data analysis, probably the most important feature of a query is its ability to be as complex as possible by handling conditional statements. This makes it possible to use a view instead of a table in operations and expressions that would complicate the code or structure of a table. When creating a view, in its SELECT statement, you can perform column selections, order them, and set criteria to exclude some records.

View Maintenance

Introduction

In Transact-SQL, a view is considered an object. As such, it can be viewed, changed, or deleted. Viewing a view primarily consists of visiting its code.

In the Enterprise Managed, to see the code of a view, in the right frame, you can either double-click the name of the view or right-click it and click Properties. This would open the View Properties dialog box:

In the Enterprise Manager, to see the visual structure of a view, you should open it in Design View. To do this, you can right-click the name of the view in the right frame and click Design View. This would open the Design View window of the view that resembles the New View:

In the Server Explorer, to see the structure of a view, right-click it and click Design View.

Modifying a View

After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or code a view. This is referred to as altering a view.

In the Enterprise Manager, to modify a view, you use its code or its structure. If you had opened the View Properties dialog box, you can edit the code of the in the Text control. After editing the view's code, you can click OK (or click Apply and Cancel, or Apply and OK).

If you had open the Design View window of the view, to remove a column, you can remove the check mark on its left in the top section of the window. You can also delete its name in the columns under the Column column. As a third alternative, you can delete its name directly in the SELECT statement. After making the necessary modifications, you can save and close the window.

To programmatically modify a view, in a Query window, use the following formula:

ALTER VIEW ViewName
AS
SELECT Statement

You start the alteration with the ALTER VIEW expression followed by the name of the view. After the name of the view, use the AS keyword to specify that you are ready to show the change. After the AS keyword, you can then define the view as you see fit. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement.

Here is an example of the previously create view but that removes the CarYear column and add two new columns:

ALTER VIEW CarIdentification
AS
SELECT TagNumber, Make, Model, Available FROM Cars
GO

Here is an example of executing the view:

Deleting a View

Instead of modifying a view, if you find it altogether useless, you can remove it from its database.

To delete a view in the Enterprise Manager or Server Explorer, right-click it and click Delete. You would be given the opportunity to confirm your intention or to change your mind.

To programmatically delete a view, use the DROP VIEW expression in the following syntax:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. You will not be warned before the interpreter deletes the view.