July 14, 2016 at 4:52 pm
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
July 14, 2016 at 6:30 pm
Fair enough, Tom, but I think it makes perfect sense the way MS has defined this and it works. The view references 6 columns in one table, and since they allow NULL by default, this makes sense and adheres to the definitions of the product.
July 14, 2016 at 7:02 pm
TomThomson (7/14/2016)
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. . . .
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 [Steve] 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, why don't you file a bug? If you for whatever reason do not want to do that, I might take on that.
July 15, 2016 at 11:40 am
This QOTD should have a disclaimer:
This schema blatantly violates second normal form and is presented for demonstration purposes only. Do not use this schema as part of class-scheduling system. No actual databases were mangled in the production of this question.
(Was that bothering anyone else?)
July 15, 2016 at 1:08 pm
Stephanie Giovannini (7/15/2016)
This QOTD should have a disclaimer:This schema blatantly violates second normal form and is presented for demonstration purposes only. Do not use this schema as part of class-scheduling system. No actual databases were mangled in the production of this question.
(Was that bothering anyone else?)
Nope. It seemed pretty clear it was an example for the purpose of the question. Additionally, it is far from the terrible stuff i see in the wild as a consultant. It really is probably about 10x better design than most things I see out there.
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply