Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-04 : 22:27:13
|
| Christine writes "In SQL2000, are there any benefits or drawbacks to using INSERT/UPDATE commands on a VIEW rather than the TABLE the view is derived from? Is "INSERT my_Table (Col1, Col2) VALUES ('A','B')" any more or less efficient than "INSERT view_of_my_Table (Col1, Col2) VALUES ('A','B')"?" |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-04 : 23:17:51
|
| Christine,The benefits to using views for Insert/Update is that of data integrity, security and data independance.a) Data Integrity... You have a User that is only allowed to work on a subset of the base table. With a view defined for this subset he/she can only Insert/Update records that match the constraints defined on the view.b) Security.. As above etc..c) Data Independance...Minor changes to the base table can be encapulated from the Application and User through the view mechanism.A view or virtual relation, should inherit (integrity inheritance) all the base tables contaraints plus the constraints used to formulate the view. The constraint on the view is termed a "Derived Contraint"Unfortunately, SQL's (the language) lack of database integrity (ie A Constraint across the entire database) disallows it to propogate the view's updates to the underlying base tables. This means you are limited to updating just a single base table.One of the options given when creating views in SQL Server is the "WITH CHECK OPTION". As you can see this shouldn't be an option at all but mandatory! Look up "Create View" in BOL.As for efficiency.. It will be slower because of the additional constraints. Personally I think that is a small price to pay for data integrity and security.DavidMTomorrow is the same day as Today was the day before. |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-04 : 23:19:57
|
| gee - comprehensive answer - and I was just going to say "yip"--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
INSERT/UPDATE into a VIEW ?