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 is given below:

  1. A View can bring data from multiple tables by using adequate joins and while bringing so, it may require complex filters and calculated data to form the required result set. In user point of view, all these complexities are hidden and data is queried from a single table.
  2. Some time for security purposes, access to the table and its internal details like table schema and relationships are not given to the database user. All they have is, access to a view without the knowledge of what tables are actually exists in the database.
  3. Using the view, you can restrict an user to update only some portions of the records.

2. Sql view Example

Have a look at the below picture which shows a Sql view definition:

Creating a View
Fig.1 - Creating a view

So, to create a view, just form a select query then use the create view statement on the top (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, we can see a new database view created for us.

In the above example, we created a view from the table Titles, which is actually 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:

Selecting Data From a 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 a view when data is taken from 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:

SQL View with Three Tables Joint together
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 colour and the data taken from the all three tables are marked in Red colour. Note that how the aliases T, Au, TA are used in the FROM clause and how they referred in the select clause of the query. Now, have a look at the below example, which queries data from the view:

Querying 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 actually queried from three different tables through table joins in the background. This is how a view hides the complexity behind it in the user perspective.

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 the picture above, 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 database 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 the 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 computed columns are not possible.
  2. Updating the data that belongs to only one 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 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.

A Successful Update through view
Fig.6 - A Successful Update through view

2) In the below example, the update through the view “vTitles” is failed because the AuthorName column is a computed column. Have a 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.

Update through view Failed because of derived column
Fig.7 - Update through view 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 of “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.

Updating columns from different table
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 because of the existence 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 keeping in mind that the view is created for the state of UT and view becomes empty after the update as there is no state of OH.

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:

Creating 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:
The check option restricts the update as it affects the view content
Fig.11 - The check option restricts the update as it affects the view content

[ No Downloads for this Article ]

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)