Creating and Using SQL Server View

1. Introduction

A View is nothing but a select query with a name given to it or we can simply say a view is a Named Query. Ok. Why do we need a view? There can be many answers for this. Some of the important stuff I feel is:

1. A view can combine data from multiple tables using adequate joins and while bringing it may require complex filters and calculated data to form the required result set. From a user's point of view, all these complexities are hidden data queried from a single table.
2. Sometimes for security purposes, access to the table, table structures and table relationships are not given to the database user. All they have is access to a view not knowing what tables actually exist in the database.
3. Using the view, you can restrict the user update only portions of the records.

2. SQL View Example

Have a look at the following picture that shows a SQL view definition:

So, to create a view, just form a select query then use the create view statement in the top as shown in the preceding picture. Many people use the prefix “v” before the view name to denote that the database object is a view. Once the statement is executed a database view object will be created.

In the preceding example we created a view from the table Titles, that is a Microsoft, supplied sample table available in the Pubs Database. Once a view is created, the data can be queried from it in the same way as the data is queried from an existing table. The following is an example showing retrieval of data from the view:

 

The SQL script is below:

-- Make sure Microsoft Pubs
-- database is avaiable in your DB server
Use pubs;
Create view vTitles as
Select
Title, type, Royalty,
(ytd_sales/12) as Monthly_Avg,
(price * 0.05) as discount_price
from titles; 

Select top 5 Title, Monthly_Avg from vTitles;
Drop view vTitles;

3. SQL View - Join Two Table

Sometimes it will be very useful to create the view when data is taken from two or more tables. Later the data can be queried from the view as it is queried from a single table. Have a look at the following example:

In the preceding example, first the view that was created in the previous example is dropped. Then a view is created on a select query, that takes data from each of three tables. The two inner joins are shown in blue color and the data taken from all three tables are marked in Red color. Note that how the aliases T, Au, TA are used in the FROM clause and how they are referred to in the select statement. Now have a look at the following example that queries data from the view:

 

Surprisingly, the aliases are gone. And when the client uses the query from this view they are not aware of the fact that the data is queried from multiple tables using table joins in the background. This is how I am saying it hides the complexity in the client or the db user's point of view.

The SQL Script is below:

Drop View vTitles;
Go
Create view vTitles as
Select
    T.Title, Type, T.Royalty,
    (T.ytd_sales / 12) as Monthly_SalesAvg,
    (T.Price * 0.05) as Discount_price,
    Au.au_fname + ', ' + Au.au_lname as AuthorName,
    TA.RoyaltyPer as RoyaltyPercent
From Titles T
Inner Join TitleAuthor TA
On T.title_id = TA.title_id
Inner Join Authors Au
on TA.au_id = Au.au_id;
Go 

Select AuthorName, Title,
Monthly_SalesAvg, RoyaltyPercent
From vTitles
Order by AuthorName;

4. Updating through SQL View

The data in the database can be updated using the view also. But, updating the data with a SQL view has some limitations. Consider the previous example again by looking at the following picture:

In this picture, the marker 1 denotes the calculated or computed columns. That means the data taken from the data table is computed using arithmetic or data manipulation operation. For example, the Discount_price column is computed by multiplying the value 0.05 with the db column Price. Similarly, the columns Monthly_SalesAvg, AuthorName are also computed like this. The preceding picture also shows that the data is taken from each of three tables and you can see that in the From Clause is marked as 2, 3, and 4.

The SQL View has some limitations when updating the data. The limitations are:

1. Updating the database using computed columns are not possible.
2. Update the data that belongs to one specific table can be possible. It is not possible to update data that comes from two different tables.

Now let us look at some examples.

1. The following example tries to update the columns Type and Royalty through the view vTitles. Since the columns are coming from the same table called “Titles”, this update is allowed.
   
   
   
2. In the following example, the update that uses the view “vTitles” fails because the AuthorName column is a computed column. Have look at the view definition and you can see that the AuthorName column is formed by combining the two columns au_fname and au_lname from the table Authors. The Error “Msg 4406, Level 16, State 1” specifies here that the update is not successful since it tries to update a computed column.
   
   
   
   
3. The Final Example is shown below. In this example we are trying to update the column from two different tables. The Column “type” is from the table “Titles” and the column “RoyaltyPercent” is from the table “TitleAuthor”. Since this update does not obey the rule, “update should be in a Single table” the update operation fails. This is shown as an error in the message “Msg 4406, Level 16, State 1” marked as 2 in the following picture.
   
   
   

5. SQL view with check option

Before we talk about the usage of the check option, have a look at the following picture:



The select portion of the vAuthors returns two rows based on the where condition. So, through this view you can update only two rows. OK, what if somebody updates the view changing the state. For example something like the following one:

Update vAuthors set state = 'OH';

These kinds of updates succeed and at the same time, only two rows are affected since that is the entire content of the view. What happens when somebody queries something from the view? The query does not return anything since the view definition does not return any data. Look at the update statement again and note that the view is created for the state UT and the view becomes empty after the update.

To avoid the preceding said situation, one can create a view with the check option. The following example shows the creation of the view with the check option:



If you try with the update again then the following shown error will be reported since the check option guards the view: