Views vs Tables
-
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query. Using MSSQL 2008R2. Thanks
Eric
-
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query. Using MSSQL 2008R2. Thanks
Eric
You may have misunderstodd, or he's wrong. I've never heard of inserting into views, a view is essentially readonly, you have no idea how the data feeding it is structured -- one of the benefits of views. Then again databases have advanced quite a bit in the last twenty years and I may be behind the times. It may be that some versions of some databases support it in some cases, but I would never do it.
-
You may have misunderstodd, or he's wrong. I've never heard of inserting into views, a view is essentially readonly, you have no idea how the data feeding it is structured -- one of the benefits of views. Then again databases have advanced quite a bit in the last twenty years and I may be behind the times. It may be that some versions of some databases support it in some cases, but I would never do it.
I had the same thoughts. You "can" do it in MS SQL 2008R2 but i never have... It seems to me to be a really bad practice as the view can (and does in my case) span multiple containers & servers.
Eric
-
I had the same thoughts. You "can" do it in MS SQL 2008R2 but i never have... It seems to me to be a really bad practice as the view can (and does in my case) span multiple containers & servers.
Eric
Eric Whitmore wrote:
a really bad practice
Yes, it is. It's got to be less efficient as the engine has to figure out how to do it and in a great many cases it simply can't. Plus it's not database agnostic, so why go to the trouble when inserting to tables works everywhere? "Simplify. Simplify." -- Thoreau
-
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query. Using MSSQL 2008R2. Thanks
Eric
Since a view can become out of sync with the underlying table, it is not a good idea to insert through a view, [sarcasm]unless you are using MS Access[/sarcasm]. Another problem is, if there are multiple tables in the view things go wrong quickly. Now if he would have said you should use a stored procedure to do the insert then I would say he has a point. However it is not wrong to insert directly into a table.
-
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query. Using MSSQL 2008R2. Thanks
Eric
Eric Whitmore wrote:
if I do "inserts" into "tables" I am doing it "wrong
You should ask why. There could be an architectural reason such as an intent to move to another schema or legacy reasons. Could just be an odd way to provide a database layer though. For that though I use stored procs.
-
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query. Using MSSQL 2008R2. Thanks
Eric
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement. PieBald You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity RAH
-
Eric Whitmore wrote:
if I do "inserts" into "tables" I am doing it "wrong
You should ask why. There could be an architectural reason such as an intent to move to another schema or legacy reasons. Could just be an odd way to provide a database layer though. For that though I use stored procs.
jschell wrote:
I use stored procs
Right, I don't (willingly), but I know many do and, of the two techniques, procedures make more sense.
-
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement. PieBald You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
in the 90s
At that time I was using Oracle (6) and I remember asking the DBAs why the views (there weren't many) had an
_vsuffix and the response was "to remind the developers that they are views and therefore readonly". -
Mycroft Holmes wrote:
in the 90s
At that time I was using Oracle (6) and I remember asking the DBAs why the views (there weren't many) had an
_vsuffix and the response was "to remind the developers that they are views and therefore readonly".I think I was working with MSSQL 6.5! Try creating a view of only 1 table, no joins, and inserting/editing a record. Now add an inner join and repeat. Even though I have SSMS open I'll not bother as it is a complete WOTAM.
Never underestimate the power of human stupidity RAH
-
You may have misunderstodd, or he's wrong. I've never heard of inserting into views, a view is essentially readonly, you have no idea how the data feeding it is structured -- one of the benefits of views. Then again databases have advanced quite a bit in the last twenty years and I may be behind the times. It may be that some versions of some databases support it in some cases, but I would never do it.
I recently tested updates on a mock view, in SQL Server 2008, which contained a join between two tables. I did this because where I work the majority of what our users see is through views and they occasionally need to update what they see. I read up on it beforehand and all the documentation pointed to this being possible as long as it was unambiguous as to which row in which table was being updated. The update did work - I then tried to break it and succeeded by using an aggregate function in the view so that there was ambiguity in what would be updated. It can be useful to insert or update a view, since if one is passing a view into a control then it is easier to perform an update, on the source of that control, than to have to attach lots of meta data documenting which tables to update.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I recently tested updates on a mock view, in SQL Server 2008, which contained a join between two tables. I did this because where I work the majority of what our users see is through views and they occasionally need to update what they see. I read up on it beforehand and all the documentation pointed to this being possible as long as it was unambiguous as to which row in which table was being updated. The update did work - I then tried to break it and succeeded by using an aggregate function in the view so that there was ambiguity in what would be updated. It can be useful to insert or update a view, since if one is passing a view into a control then it is easier to perform an update, on the source of that control, than to have to attach lots of meta data documenting which tables to update.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
passing a view into a control
But that's not a good idea. It doesn't lead to a properly layered application.
GuyThiebaut wrote:
aggregate function
Or a UNION or a CASE , probably if there's CAST or CONVERT ... I suppose it requires that there be a primary key as well.
-
GuyThiebaut wrote:
passing a view into a control
But that's not a good idea. It doesn't lead to a properly layered application.
GuyThiebaut wrote:
aggregate function
Or a UNION or a CASE , probably if there's CAST or CONVERT ... I suppose it requires that there be a primary key as well.
"But that's not a good idea. It doesn't lead to a properly layered application." - I don't understand why using a view in a control would do this, could you let me know why?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement. PieBald You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
I recently tested updates on a mock view, in SQL Server 2008, which contained a join between two tables. I did this because where I work the majority of what our users see is through views and they occasionally need to update what they see. I read up on it beforehand and all the documentation pointed to this being possible as long as it was unambiguous as to which row in which table was being updated. The update did work - I then tried to break it and succeeded by using an aggregate function in the view so that there was ambiguity in what would be updated. It can be useful to insert or update a view, since if one is passing a view into a control then it is easier to perform an update, on the source of that control, than to have to attach lots of meta data documenting which tables to update.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
since if one is passing a view into a control then it is easier to perform an updat
Control? As in a GUI control? For small systems with stand along apps that might be appropriate. For anything larger one should have a database layer. And of course it won't work at all for a web app.
-
"But that's not a good idea. It doesn't lead to a properly layered application." - I don't understand why using a view in a control would do this, could you let me know why?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Mycroft Holmes wrote:
You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Jörgen Andersson wrote:
It's quite useful if you want to filter the contents of a table for a certain group of users.
Why would you do that in the database versus the application layer? And of course in general when you say 'user' if you mean an actual database user then there can be negative impacts such as licensing.
-
Mycroft Holmes wrote:
You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Jörgen Andersson wrote:
f you want to filter the contents of a table for a certain group of users
Sure but that is one of the uses of a view - a very old fashioned use, but still valid. Who puts authorisation logic at that level these days. Apps no longer have to rely on the database for authorisation, very 80s :laugh:
Never underestimate the power of human stupidity RAH
-
GuyThiebaut wrote:
I don't understand why using a view in a control would do this, could you let me know why?
Because if you use the view in the GUI then there is no database layer. Nor presumably a business layer for that matter.
There is a database layer - the control is populated via a query from this layer. Any updates to this view are then passed through this layer via update commands. So we can lock down particular columns and perform verification on the values before update so that we both have a business and database layer. So I am still not getting what is wrong with using the results of a view in a GUI.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Jörgen Andersson wrote:
It's quite useful if you want to filter the contents of a table for a certain group of users.
Why would you do that in the database versus the application layer? And of course in general when you say 'user' if you mean an actual database user then there can be negative impacts such as licensing.
Because some companies are quite divided. The right place to do it is in the application, but I've been working for companies where the database department were responsible for that one department couldn't see the same columns as another department. It's a lot easier to keep track on this with a view than with column permissions. When it comes to licensing it doesn't matter. It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull