Introduction

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 use the Object Explorer (Microsoft SQL Server Management Studio), a query window (Microsoft SQL Server Management Studio), or the Server Explorer (Microsoft Visual Studio). Before starting the view, you would have to specify the table(s) that would be involved. To create a view from the Object Explorer or the Server Explorer, you can expand the database, right-click Views and click New View or Add New View. This would open the Add Table dialog box:

The basic functionality of this dialog box is exactly the same as we reviewed for data analysis in the previous lesson:

• 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 in the previous lesson, we saw that you could add more than one existing table. the same way, you can add more than one view or functions
• After selecting the source(s), you can click Close on the Add Table dialog box
• After selecting the objects, as we saw in the previous lesson, they would display in the window
• As seen in the previous lesson, if you are using more than one table and they are not (yet) related, you can drag a column from one table and drop it on another table to create a JOIN between them
• As we saw in previous lessons, 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
• 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.

The 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, in the Microsoft SQL Server Management Studio you can click the Execute SQL button or in Microsoft Visual Studio, you can right-click the view and click Execute SQL. This would cause the bottom section of the view to display the results of the query. Here is an example:

As reviewed during data analysis and when creating joins in previous lessons, you can add conditions in a view to make it isolate only some records. Here is an example:

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: a node would be created for it and its name would appear in the Views node of its database.

As stated already, one of the reasons for creating a view is to be able to use it over and over again. To achieve this, the view must be saved. Like most objects in Microsoft SQL Server, a view must have a name and it is saved as its own object. To save a view from the 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.

As stated already, a view is a technique of selecting records to view or use over an over again. After a view has been created, you can open it. You have two main options.

• To see the structure of a view, such as the table(s) on which it is based and the relationships, if any that compose it, in the Object Explorer, right-click the view and click Design
• To see the SQL code that makes up a view, in the Object Explorer, right-click the view and click Edit

Executing a view consists of seeing its results. To do this, you have various options. To view the results of a view:

• Open an empty query window associated with the database that contains the view. In the query window, write a SELECT statement using the same formulas and rules we saw for tables. Here is an example:
   
  
• From the Object Explorer, expand the database and its Views node. Right-click the name of the view and click Open View