difference in simple insert query and inserting through a view into a table

Question

i am studying about views in sql and somewhat confused and having the following query :

i have a table 'item' and need to insert data into it. am using two ways, one without view and the other using a view.

CREATE VIEW vw_item
SELECT * FROM item

1. Inserting via the view:

   INSERT INTO vw_item values(...)
   

2. Inserting via the table:

   INSERT INTO item values(...)
   

What's the difference in query 1 and 2? That is, directly inserting into the table & using a view to insert into the table.

Is there a difference in insertion only when we use CHECK OPTION while creating the view?

Answer 1

There is no difference between the two INSERT statements. Views can be updateable, but there are restrictions on what makes views updateable.

The CHECK OPTION prevents changes that do not meet the view's criteria. If your view example had a WHERE clause, the column(s) involved could not be updated -- but other columns in the table could be.

Reference

• CREATE VIEW (Transact-SQL)

Answer 2

specifics on MS-SQL:

1. OMG Ponies' answer has the bulk of what you're looking for, start there.

2. Regarding how a VIEW works, according to How much space does a view take up in Microsoft SQL Server, the view only "materializes" (i.e. to retrieve data) when it is called, unless it is an Indexed View.

3. Having an indexed view adds additional overhead with inserts/updates/deletes. Source: http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server-Page-4.htm

4. Misc. info on optimizing indexed views: http://technet.microsoft.com/en-us/library/cc917715.aspx

Some specifics on MySQL (maybe useful to others curious about VIEWs): http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

Other database servers probably have slight differences with performance/indexing/updatablility, so keep that in mind for your particular database.

Answer 3

CReate table param1(id int,num int);
CReate table param2(id int,num int);
INSERT INTO param1 VALUES(1,1),(2,2),(3,3);
INSERT INTO param2 VALUES(1,1),(4,4),(3,3); 


CREATE VIEW vie(id) AS 
SELECT Id FROM param1 WHERE num IN (SELECT num FROM param2); 

INSERT INTO  vie VALUES(100); 
SELECT * FROM vie;
SELECt * FROM param1;