Overview of Views
When studying data analysis, we saw that a query was a technique of isolating a series of columns and/or records of a table. Although this is usually done for the purpose of data analysis, it 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 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 will 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:
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 the previous lesson, you can add conditions in a view to make it isolate only some records. Here is an example:
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. In our lessons, here are the rules we will use to name our views:
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, 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.
Executing a view consists of seeing its results. To do this, you have various options. To view the results of a view:
To programmatically create a view, you use the following SQL syntax:
CREATE VIEW ViewName AS SELECT Statement
If you are using Microsoft SQL Server Management Studio, it can generate skeleton code of a view for you. To use it, first open an empty query window. Display the Template Explorer. In the Template Explorer, expand the View node. From the View node, drag Create View and drop it in the query window.
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 for view names. 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 SQL statement, 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 dbo.ListOfMen AS SELECT dbo.Sexes.Sex, dbo.Persons.FirstName, dbo.Persons.LastName FROM dbo.Genders INNER JOIN dbo.Persons ON dbo.Sexes.SexID = dbo.Persons.SexID WHERE (dbo.Sexes.Sex = 'Male'); GO
After creating the SQL statement that defines the view, you must execute the statement. If using a query window in Microsoft SQL Server Management Studio, you can do this by pressing F5. Once the statement is executed, its name is automatically added to the Views node of its database even if you do not save its code.
After creating a view, it shares many of the characteristics of a table. For example, a view has its own columns although the columns are actually tied to the table(s) that hold(s) the original data. Treated as a table, you can access the columns of a view using a SELECT statement. This means that you can access one, a few, or all of the columns. Here is an example that accesses all columns of a view:
SELECT PayrollPreparation.* FROM PayrollPreparation;
In Transact-SQL, a view is considered an object. As such, it can be viewed, changed, or deleted. Like any regular object, a view has its own characteristics. To see them in Microsoft SQL Server Management Studio, you can right-click the view and click Properties. A View Properties dialog box would come up. It can give you information such as the name of the database the view belongs to, the date the view was created, etc.
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 the code of a view. This is referred to as altering a view. You have various options:
The basic formula to programmatically modify a view is:
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 perform 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.
In the view we created to show a list of men of a table, we included a column for the sex. This column is useless or redundant because we already know that the list includes only men. Here is an example of altering the view to remove (or rather omit) the Sex column of the Persons table:
ALTER VIEW dbo.ListOfMen AS SELECT dbo.Persons.FirstName, dbo.Persons.LastName FROM dbo.Sexes INNER JOIN dbo.Persons ON dbo.Sexes.SexID = dbo.Persons.SexID WHERE (dbo.Sexes.Sex = 'Male');
Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You have various options. To delete a view:
The formula to programmatically delete a view is:
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. If you are programmatically creating a Windows Forms application, of course you can use a conditional statement to assist the user with deciding whether to continue deleting the view or not.
As seen so far, a view is a selected list of records from a table. As you may suspect, the easiest view is probably one created from one table. Imagine you have a table of employees and you want to create a view that lists only their names. You may create a view as follows:
CREATE VIEW dbo.EmployeesNames AS SELECT FirstName, LastName, LastName + ', ' + FirstName AS FullName FROM Persons; GO
On such a view that is based on one table, you can perform data entry, using the view, rather than the table. To do this, you follow the same rules we reviewed for table data entry. Here is an example:
INSERT INTO dbo.EmployeesNames(FirstName, LastName) VALUES('Peter', 'Justice');
If you perform data entry using a view, the data you provide would be entered in the table from which the view is based. This means that the table would be updated automatically. Based on this feature, you can create a view purposely intended to update a table so that, in the view, you would include only the columns that need to be updated.
To create more complex or advanced views, you can involve functions. As always, probably the easiest functions to use are those built-in. If there is no built-in function that performs the operation you want, you can create your own. Here is an example:
USE People; GO CREATE FUNCTION dbo.GetFullName ( @FName varchar(20), @LName varchar(20) ) RETURNS varchar(41) AS BEGIN RETURN @LName + ', ' + @FName; } GO
Once you have a function you want to use, you can call it in the body of your view as you judge it necessary. Here is an example:
CREATE VIEW dbo.MyPeople AS SELECT dbo.GetFullName(FirstName, LastName) AS [Full Name], dbo.Genders.Gender FROM Genders INNER JOIN dbo.Persons ON dbo.Genders.GenderID = dbo.Persons.GenderID;
This would produce:
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 structuring 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 dbo.MenAndWomen([First Name], [Last Name], Gender) AS SELECT dbo.Persons.FirstName, dbo.Persons.LastName, dbo.Genders.Gender FROM dbo.Genders INNER JOIN dbo.Persons ON dbo.Genders.GenderID = dbo.Persons.GenderID; 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, do not specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source.
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 the 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.
Here is an example:
|Home||Copyright © 2008-2012 FunctionX|