Pages

Saturday, June 20, 2015

A myth about view TIP #102

 

I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax

“CREATE VIEW

AS

QUERY “

Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource

 

CREATE VIEW StudentSourceView
AS
SELECT StudentId,
       FirstName,
       LastName,
       Course,
       Detail
FROM tblStudentSource

Go

Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added

Indiandotnet_View_1

So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure

Indiandotnet_View_2

As shown record 2004’s FirstValue is updated to value “Updated”

 

Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!

 

Thanks

RJ

3 comments:

  1. Then what about stored procedure? can we use them alternatively? what is the difference between SP and view?

    ReplyDelete
  2. Views do not allow parameters whereas Stored Procedures do. Personally, I'd rather use a CTE within a stored procedure for the purpose of Insert and Update procedures. Views are good for generating sub set data or aggregating data from multiple tables. Even User defined functions, except for scalar functions, are better for updating, inserting and deletion then views.

    ReplyDelete
  3. We already be aware of 12 E. F. Cod rules for ideal dbms, whereas current DBMS's just follow 11.5 rules.... why? becoz of this DML is possible only in half of the views so 1/2 marks ;) reason in case if view is created using..

    CREATE VIEW Abc
    AS
    SELECT SUM(sal) as 'sumsal' from tab_abc

    on this view DML cant work. as column sumsal in view is aggrigate value and not exist anywhere in this form so no DML ia possible.

    but, I personally feel DBMS providers should create 2 different categorized for simple and complex views to follow rest of .5 point. Mr.Cod also may have not thougt about this complexity before writing this rule ;)

    ReplyDelete