February 18, 2010 at 4:42 am
I take the business of customer data integrity very seriously - its no joking matter and there should be no room for possible corruption of that data imho.
Lol - the cursor and the quirky update are to the relational model the same - they both break it.
Sets have no order, they are operated on in their entirity in one go - not a row by row!
Quirky update is no more set orientated than a cursor.
Tony.
February 18, 2010 at 5:20 am
tony rogerson (2/18/2010)
I take the business of customer data integrity very seriously - its no joking matter and there should be no room for possible corruption of that data imho.
A laudable sentiment. But that's no excuse for personal remarks. We happen to differ on quite a small point, and I don't propose to get too serious over it.
tony rogerson (2/18/2010)
Lol - the cursor and the quirky update are to the relational model the same - they both break it.
Maybe, I don't know - I'm not an expert on the Relational Model.
tony rogerson (2/18/2010)
Sets have no order, they are operated on in their entirity in one go - not a row by row!
In principle, yes. Of course all computer operations come down to row-by-row processing if you dig deep enough. Everything SQL Server does is row-by-row at some level, but that doesn't mean that some methods aren't much more set-orientated than others.
tony rogerson (2/18/2010)
Quirky update is no more set orientated than a cursor.
I don't think there can be much doubt that QU is more set-orientated. Not that it really matters, of course. Even Jeff has been known to call it a pseudo-cursor method. It just happens to be orders of magnitude more efficient...!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2010 at 8:48 am
SwePeso (11/13/2009)
Make your vioce heardVote on Connect here
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388
It's a pity it's asking for the wrong enhancement, instead of for implementaton of range expressions generally.
Tom
February 23, 2010 at 12:36 pm
tony rogerson (2/18/2010)
Sets have no order ...
Not true, sets can have order, they're called "ordered sets" and they've been around for many decades at least (Church in the 30's, I think). They are sometimes called "Lists", but I dislike that term for tuple-sets.
..., they are operated on in their entirity in one go - not a row by row!
Well you're mixing your paradigms here, "sets have no order" is an allusion to mathematical Set Theory. "Operated on" is invoking at least procedural logic, though more likely CS state-transformation concepts, but there is no such thing as state-transformational operations (whether whole-set, or element by element) in Set Theory.
And "in their entirety ... not a row by row", is only correct if it is an allusion to the Declarative vs. Procedural dichotomies in SQL itself, a related concept, but still distinct from the Set vs. non-Set discussion. Almost all disciplines that I know that uses sets, including Relational Theory, allow both, usually distinguished as a "Calculus" and an "Algebra". Technically both are set-oriented, but the element-by-element modes (the "algebras") lend themselves much more to procedural (or "imperative") implementations.
Quirky update is no more set orientated than a cursor.
It arguably perfectly set-oriented since it is after all, oriented to operate on sets, right? What it is not is purely Declarative code, but then neither is it truly procedural code either, since it cannot branch, and cannot have general looping control structures, but only a single, finite and extremely restricted "pseudo-loop".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 12:48 pm
tony rogerson (2/18/2010)
Yes - I do mean a cursor; a static one and not a "update where current".I don't honestly know what your problem is here Paul - even Jeff has found other problems with his routine (see a couple of posts up).
IF you are saying you are happy for this to be used in a production environment then frankly should be sacked because it is clear and proven that its not repeatable behaviour that is GUARENTEED to work.
Hey - less just drawn the line; I believe people should be told they shouldn't be using the quirky update so when I see people trying to use it I'll be professional and tell them DON'T :w00t:
Tony.
Nope.... I didn't find any problems. What have been found are new ways to keep it from working to begin with. The examples are someone's attempt to say it doesn't work and my attempt to say they didn't follow the rules to begin with. It's just like any other code... you have to follow the rules.
I'm at work right now and I have an after work appointment but I'll post some very well documented code that doesn't work simply because someone broke the rules or did something that isn't logical.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2010 at 12:50 pm
In the Relational Model (the context I was speaking in because we are on a SQL Server forum and its SQL we are talking about) sets have no order.
Again... we are talking about the Relational Model (the context I was speaking in because its SQL we are talking about).
In our context - SQL and the Relational Model the operation happens in one go; feel free to go read Codd, Date and Jim Gray.
There are clauses within standard SQL that allow ordered operations but that breaks Relational Theory and SQL is just a sub-langauge that in parts badly implements relational theory.
The QU is not set orientated because it relies on the physical order of the rows in the table by praying it will use an ordered index scan, in the Relational Model (the context we all work in within the Database field) sets have no order....
There are many things both identified and unidentified that break the quirky update.
If you use it then you are doing so at your own (and your clients) peril; as a professional of over 23 years in IT that is what I have to say; if one knows something can break because its being used for a purpose it was not intended for, it has undefined behaviour that may give unpredicable results then what else would any professional do?
I remember 4.21 - I coded in it; we had this to make capturing the result from incrementing a counter so we didn't have to do UPDATE and then a subsequent SELECT.
Tony.
February 23, 2010 at 1:12 pm
tony rogerson (2/18/2010)
Yes - I do mean a cursor; a static one and not a "update where current".I don't honestly know what your problem is here Paul - even Jeff has found other problems with his routine (see a couple of posts up).
IF you are saying you are happy for this to be used in a production environment then frankly should be sacked because it is clear and proven that its not repeatable behaviour that is GUARENTEED to work.
Hey - less just drawn the line; I believe people should be told they shouldn't be using the quirky update so when I see people trying to use it I'll be professional and tell them DON'T :w00t:
Tony.
Considering that hardware failures are possible, cosmic rays exist, and the laws of thermodynamics include the concept of increasing entropy in ordered systems, nothing any human being does can ever have fully guaranteed results with complete repeatability.
Thus, you should be fired from every job you might ever hold.
In the meantime, the rest of us will be satisfied with things that work with acceptable reliability in every circumstance they've been tested in, following readily specified and understood rules. After all, what's good enough for nuclear physics is good enough for software engineering.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2010 at 1:48 pm
tonyrogerson-498793 (2/23/2010)
In our context - SQL and the Relational Model the operation happens in one go; feel free to go read Codd, Date and Jim Gray.
Well, I did read them, when they were originally published, in fact. (Why do Relational Purists always think that they are the only ones who have ever read this stuff?) And I would be very interested to know where you think that either Codd or Gray said this.
And just so there's no equivocation here, what I am asking is "Where did Codd or Gray say that SQL code must change the data-state of a database without being able to syntactically or semantically make reference to the intermediate state of such an operation?" That's "SQL" and not the "Relational Model", because according to both Codd and Date, SQL is NOT a "Relational Database" and does not truly implement the Relational Model and according to the SQL committee, neither is it limited to it, therefore it is not relevant to Jeff's SQL article about a using SQL in a SQL database, SQL Sever, on this SQLServer forum. Also, we are talking SQL language code that exploits intermediate data during a data-state transformation, so neither transactions (because we could wrap any of these in a valid transaction) nor the syntactical delineation of Codd's Relational Calculus are relevant here.
Are far as I know, neither Codd nor Gray ever said anything like this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 4:34 pm
GSquared (2/23/2010)
Considering that hardware failures are possible, cosmic rays exist, and the laws of thermodynamics include the concept of increasing entropy in ordered systems, nothing any human being does can ever have fully guaranteed results with complete repeatability.Thus, you should be fired from every job you might ever hold.
Pure gold :laugh:
GSquared (2/23/2010)
In the meantime, the rest of us will be satisfied with things that work with acceptable reliability in every circumstance they've been tested in, following readily specified and understood rules. After all, what's good enough for nuclear physics is good enough for software engineering.
Quite.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2010 at 6:10 pm
And here in a nutshell is demonstrated exactly what has been wrong with the academic discipline of Relational Theory for the last 30 years: A complete inability to grasp (or refusal to accept?) the difference between Theory and Application, and what their appropriate roles are academically and in our industry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 7:05 pm
tony rogerson (2/18/2010)
IF you are saying you are happy for this to be used in a production environment then frankly should be sacked because it is clear and proven that its not repeatable behaviour that is GUARENTEED to work.Tony.
It seems to me that it's been proven to work and to survive upgrades at least as well as some well-documented supported features - in fact better than several that have magically changed just because MS felt like changing them with no notice.
As for your sacked remark - well, there are limits: if you worked for me you would be out of the door right now (whether or not I agreed with your advice not to use the thing). Jeff is clearly advising that thorough checks be included to ensure that if the thing ceases to work that is detected before it causes a problem - which is exactly what should be done for ALL code: it's called "defensive coding", is mandatory in any mission-critical application, and its use renders your extreme personal attack nonsensical. Your disgraceful comment clearly takes no account of that.
Tom
February 23, 2010 at 7:20 pm
tony rogerson (2/18/2010)
Sets have no order, they are operated on in their entirity in one go - not a row by row!Quirky update is no more set orientated than a cursor.
Tony.
And SQL is no more set oriented than my kilt! It allows duplicate rows in tables, so they can not be sets and can not be relations, they must be something else (I think that something else is called "tables"). Please tell me where I can find a set as a primitive object in the SQL world, or accept that SQL is table (or "result-set" - and a result-set is no more a set than a table is) oriented, not set oriented or relation oriented.
Tom
February 23, 2010 at 7:30 pm
RBarryYoung (2/23/2010)
tonyrogerson-498793 (2/23/2010)
In our context - SQL and the Relational Model the operation happens in one go; feel free to go read Codd, Date and Jim Gray.Well, I did read them, when they were originally published, in fact. (Why do Relational Purists always think that they are the only ones who have ever read this stuff?) And I would be very interested to know where you think that either Codd or Gray said this.
And just so there's no equivocation here, what I am asking is "Where did Codd or Gray say that SQL code must change the data-state of a database without being able to syntactically or semantically make reference to the intermediate state of such an operation?" That's "SQL" and not the "Relational Model", because according to both Codd and Date, SQL is NOT a "Relational Database" and does not truly implement the Relational Model and according to the SQL committee, neither is it limited to it, therefore it is not relevant to Jeff's SQL article about a using SQL in a SQL database, SQL Sever, on this SQLServer forum. Also, we are talking SQL language code that exploits intermediate data during a data-state transformation, so neither transactions (because we could wrap any of these in a valid transaction) nor the syntactical delineation of Codd's Relational Calculus are relevant here.
Are far as I know, neither Codd nor Gray ever said anything like this.
Barry, I think I'm falling in love with you!:-P If only the "relational purists" (none of whom, so far as I have seen, would recognise a relational algebra or a relational calculus if it got up and bit them) understood this we would hear (and read) a lot less rubbish.
Tom
February 23, 2010 at 8:19 pm
RBarryYoung (2/23/2010)
And here in a nutshell is demonstrated exactly what has been wrong with the academic discipline of Relational Theory for the last 30 years: A complete inability to grasp (or refusal to accept?) the difference between Theory and Application, and what their appropriate roles are academically and in our industry.
No, this isn't what's wrong with the academic discipline at all, it's what's wrong with completely incompetent attempts to apply part (but not all - the purists are always a bit selective) of the academic theory to areas where the academics have recognised that it is inapplicable (or at least requires substantial modification to be applicable). I think we would agree, though, that an OLTP system would be far better founded on an SQL model than on a purely relational one (unless Haskell and monads have advanced much further since I last looked at them than I believe they have).
Twentyfive years ago I was working on massively parallel systems, and the concept of a relational (not SQL but actually relational in the mathematical logic sense) was extremely useful as a starting point: it provided enormous freedom to apply parallel algorithms, something which SQL can never provide (for example SQL needs a max parallelism "hint", and quirky update is not a good idea for some of its usual applications when working with partitioned tables); the big question was how, if we started from a purely relational point of view for the underlying data handling engine, we could implement SQL on top of it without losing all the advantages of being "pure" in the bottom layer. We got some good answers, published some interesting papers (and a lot of boring ones), got some valuable patents (under the European system, where you actually have to have something new and useful with no prior art, unlike the US one where you can still patent the windmill) and a few small (useful, but not on the scale of a full DBMS) products were released by some of the industrial partners. We would have got nowhere without the relational model as a starting point (by the relational model, I mean of course what I learnt a few decades ago as a maths student, not anything a "relational SQL" purist could imagine).
Tom
February 23, 2010 at 8:48 pm
Tom.Thomson (2/23/2010)
tony rogerson (2/18/2010)
Sets have no order, they are operated on in their entirity in one go - not a row by row!Quirky update is no more set orientated than a cursor.
Tony.
And SQL is no more set oriented than my kilt! It allows duplicate rows in tables, so they can not be sets and can not be relations, they must be something else (I think that something else is called "tables"). Please tell me where I can find a set as a primitive object in the SQL world, or accept that SQL is table (or "result-set" - and a result-set is no more a set than a table is) oriented, not set oriented or relation oriented.
Here's an even better question about Sets: where in the whole world DO they exist? The answer is: No Where, because Sets are Mathematical Constructs, and mathematical constructs and even concepts can not exist in the Material World, they can only be symbolically interpreted in it, by valid application of its Theory to it. And no, they cannot exist in a computer either, computers are still in the material world, There is no Number 8, no Sets and no True or False in them, we just apply those interpretations to them.
Now as many are no doubt eager to rebut, this is normally a petty and facile distinction for most issues, which is true. Normally, and for most issues. However, today's Relational Researchers do not seem to understand the difference between Theory and Application, and have spent the last thirty years acting like they are the same thing, which is largely the cause of the rift between them and the practitioners.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 136 through 150 (of 307 total)
You must be logged in to reply to this topic. Login to reply