Views

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.

 

Previous	Copyright © 2005-2016, FunctionX