The View Insert
-
Comments posted to this topic are about the item The View Insert
-
Nice question, Reminded me one of the most asked question by interviewers in earlier days
-
Really interesting - and by testing out, I both learned something and got it right.
Still, my preferred answer would be simply "Yes, it Works". - You input a row to the view, then cannot retrieve it by the view! Maybe it has to be like this, but is it really "fine", as well?
-
morlindk (7/14/2016)
Really interesting - and by testing out, I both learned something and got it right.Still, my preferred answer would be simply "Yes, it Works". - You input a row to the view, then cannot retrieve it by the view! Maybe it has to be like this, but is it really "fine", as well?
Agreed. I tried it out and was surprised to find that I could insert to the view but then the row could not be retrieved from the view. Good learning for me today.
-
Nice question to get the brain working early.
-
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Definitely interesting...I thought you could only insert into views if the view was schema-bound. Hooray new knowledge!
-
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
-
Very sneaky!
Thanks, Steve!
-
You are all welcome, and glad I got a good one here. This was something I learned as well.
-
Luis Cazares (7/14/2016)
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
My table has values in it specifically for the insert I just performed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (7/14/2016)
Luis Cazares (7/14/2016)
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
My table has values in it specifically for the insert I just performed.
Mine did not. It said it ran successfully but the tables are all empty. Weird.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
My Schedules table has data in it but classes does not. I figured that this is because the BuildingSchedule view only returns columns from the Schedules table. I tried updating this view to return the BuildingID from Classes and left the other return columns alone and when I ran the insert again I get this error:
Msg 4405, Level 16, State 1, Line 1View or function 'dbo.RoomCapacity' is not updatable because the modification affects multiple base tables.Which makes sense.
Thanks for the question Steve!
-
Steve Jones - SSC Editor (7/14/2016)
You are all welcome, and glad I got a good one here. This was something I learned as well.Actually, I think you got a terrible one here. A really terrible one. It's just plain wrong. Worse still, the answer is just plain stupid.
Evidently you (and presumably some people at Microsoft too, since what they implemented and released conforms to what you say, despite the page you reference specifically ruling this out with the plain and simple statement "Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table". Microsoft have clerly documented that they don't wht to be caught up in this by the incompleteness theorem, and it is clear that the updatability of views is not generally decidable (the question is equivalent to Goedel's undecidability theorem or to Turing's halting problem, so any sane implementation will allow only some subset of views, and that won't include all updateable views). MS's decision that a view would be updateable with respect to a statement meant that that statement could reference only view columns that each reference only one table, and all referenced colums would be in the same table, is a somewhat OTT restriction compared to the formal meaning, but I think it makes sense in practice (since it's mathematically proventht the formal mening can't be implemented) although it may be a bit over-restrictive. It's a pity that they implement thedidn't decision in their code, but only put it in their documentation.
The update in your question requires an update to affect the view, and if you think that doesn't mean that it references the pairs of columns in the two tables (there are six columns in two tables involved in that view, not three columns in one table) you have a very different concept of "reference" from the concept most people have.
It also indicates a remarkable concept of "updateable" for views. The usual definition is that a vew is updateable if an update statement can unambiguously define what updates must be made in the underlying tables to achieve the effect, but the action tken by SQL Server certainly doesn't achieve the update required on the view - it updates one of the underlying tables but that doesn't update the view, in the case in your question it leaves the view unchanged. If "this works fine" really meant "ithis updates one of the underlying table in a way that might sometimes but often won't also update the view as specified" this question and answer would be acceptable, but that's not what any sane person understands by "this works fine".
What we actually have here is that MS SQL is executing an update statement that alter some data, but doesn't actually update what it says it will update, and doesn't signal an error despite not achieveing the update requested and it being clearly documented by microsoft ; if you want to treat that as "it works fine" then fair enough: that's your idea of what's fine, but it's certainly not mine.
Tom
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply