1. Introduction to SQL Views

An SQL View is nothing but a select query with a name given to it or we can say a view is a ‘Named Query’. Why we need a view? There will be a lot of answers for this. Some important answers are below:

1. A View can bring data from many tables by using suitable joins and while bringing so, it may use complex filters and calculated data to form the needed result set. In user point of view, all these complications are hidden, and they feel that they are pulling data from a single table.
2. Some time for security reasons, access to the table and its private details like table schema and relations are not given to the DB users. All they have is access to a view without the knowledge of what tables are actually sitting in the DB.

3. Using the view, the DB admin can restrict an user to update only some portions of the records.

2. Creating SQL View

Have a look at the below picture which shows a SQL View definition:

So, to create a view, we 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. After we run the statement, we can see a new database view created for us.

In the above example, we created a view from the table Titles, which is a Microsoft supplied sample table present in the Pubs Database. After creating the view, we can query data from it like how we query the data from any existing table. Below is the example that shows fetching the data from the view:

The SQL script is below:

3. SQL View From Different Tables

Sometimes it is very useful to create a view from two or more tables via joins. Later we can query the data from the view as we query it from a single table. Have a look at the below example:

In the above example, first, we drop the previously created view. Then we created a new view on a select query which takes data from three tables. The above example shows the two inner joins in blue colour and the data taken from the all three tables are marked in Red colour. Note how we used the aliases T, Au, TA in the FROM clause and how we refer to it in the select clause of the query. Now, have a look at the below example, which queries data from the view:

Surprisingly, the aliases are gone out. And when a client uses the query from this view, they are not aware of the fact that the view queries the data from three separate 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:

4.Update Tables Through SQL View

One can update a table in the DB using the view. But updating the data through SQL view has some limitations. Now we will consider the past example again by looking at the below picture:

In the picture above, the marker 1 stands for the calculated or computed columns. That means the data taken from the data table is computed through arithmetic or data manipulation process. For Example, the SQL View computes the Discount_price column 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 the View takes the data from three tables and we 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. The view does not allow to update the table via computed columns.
2. The view allow to update the table via columns providing that they belongs to same table. In other words, one can update columns from more than one table.

4.1 Updating Tables Through View Examples

We created our view by joining three tables. Now, we will look at some examples which will try to update these tables through the view.

4.1.1 Update Columns From Same Table

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 , SQL Server allows this update through the View.

Below is the SQL Script:

4.1.2 Updating Computed Column Not Possible

In the below example, the update through the view vTitles fails because the AuthorName column is a computed column. Have a look at the view definition and we 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 unsuccessful as the SQL View tries to update a computed column.

Below is the SQL Script:

4.1.3 Updating Columns of More Than One Table Via View Not Possible

The Last Example is below. In this example, we are trying to update the column from two 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 fails. The picture here shows this as an error in the message “Msg 4406, Level 16, State 1” which is marked as 2.

Here is the SQL Script:

5. SQL View With Check Option

Before we talk about the usage of check option, we will look at the View creation in the below picture:

The select portion of the vAuthors SQL View returns only two rows because of the existence of the where condition. This where clause is filtering the records based on the State column. So, through this view we can update only two rows. OK, what if somebody updates the table through this view to change all the states to ‘OH’. Such an update statement is below:

These kinds of updates succeed and only two rows get affected as that is the whole content of the view. What happens when somebody queries something from the view after the above update? The query returns nothing as the view definition does not return any data. Look at the update statement again keeping in mind that the view was created for the state of UT and it becomes empty after the update as there is no state of UT.

To avoid the above said situation, one can create a View ‘ With Check Option’. The below example shows how to create a 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 scripts are in-line with the Article. Hence, No Downloads for this Article

• Ado.Net SqlCommand Example
• Hooking CustomValidator Control & Javascript

Categories: SQL Server

Tags: Updating via Views, Views, With Check Option

This site uses Akismet to reduce spam. Learn how your comment data is processed.