Friday, March 28, 2014

[ SQL 2005 ] - Creating a view and updating db through the 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 we need a view? There will be lot of answer for this. Some of the important stuff I feel is given below:

1) A view can bring data from multiple tables by using adequate joins and while bringing it may require complex filters and calculated data to form the required result set. In user point of view, all these complexities are hidden data is queried from a single table.

2) Some time for security purpose, 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 are actually exists 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 below picture which shows a Sql view definition:

Fig.1 - Creating a view

So, to create a view just form a select query then use the create view statement in the top as shown in the above 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 above example we created a view from the table Titles, which is a Microsoft, supplied sample table available in the Pubs Database. Once a view is created data can be queried from it like how we query the data from any existing table. Below is the example that shows retrieving the data from the view:

Fig.2 - Selecting Data From a View

The Sql script is below:

-- Make sure Microsoft Pubs
-- database is avaiable in your DB server
Use pubs;
Create view vTitles as
      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 the two or more different tables. Later the data can be queried from the view as it is queried from a single table. Have a look at the below example:

Fig.3 - SQL View with Three Tables Joint together

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

Fig.4 - Querying data from the view

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

The SQL Script is below:

Drop View vTitles;

Create view vTitles as
      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;

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 through SQL view has some limitations. Consider the previous example again by looking at the below picture:

Fig.5 - Joins and Calculated Columns

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

The SQL View has some limitation while updating the data. The limitations are:
1) Updating the database through computed columns are not possible.
2) Update the data that belongs to one particular table can be possibly. It is not possible to update data that comes from two different tables.

Now let us look at some examples.

1) The below example tries to update the columns Type and Royalty through the view vTitles. As the columns are coming from the same table called “Titles”, this update is allowed.

Fig.6 - A Successful Update

2) In the below example, the update through the view “vTitles” is failed because the AuthorName column is a computed column. Have look at the view definition and you can see that 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 as it tries to update a computed column.

Fig.7 - Update Failed because of derived 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”.  As this update does not obey the rule, “update should be in a Single table” the update operation got failed. This is shown as error in the message “Msg 4406, Level 16, State 1” marked as 2 in the below picture.

Fig. 8 - Updating columns from different table 

5. SQL view with check option

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

Fig. 9 - A view with Filtered Rows

The select portion of the vAuthors returns two rows based 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 below one:
Update vAuthors set state = 'OH';

These kinds of updates succeed and at the same time, only two rows gets affected as that is the whole content of the view. What happens when somebody queries something from the view? The query does not return anything as 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 view becomes empty after the update.

To avoid the above said situation, one can create a view with the check option. The below example shows creating the view with check option:

Fig. 10 - Creating view with Check Option

If you try with the update again, the below shown error will be reported as the check option guards the view:
Fig.11 - The check option restricts the update as it affects the view content

[ No Downloads for this Article ]

*** When a New Article posted, get Notification to the email that you give below: ***


The opinions expressed herein are my own personal opinions and do not represent my current or previous employer's view in anyway