Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Halloween Protection Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 12:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
L' Eomot Inversé (10/31/2012)
Meow Now (10/31/2012)
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.

UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644

Isn't life wonderful. Here we have a problem identified by IBM back in the 70s, and solved by them back in the 70s, back in the days when the US sopreme Court was still sufficiently sane to assert that software patents were illegal. Then MS gets a patent on solving it in 2000, with a patent that has no non-obvious invention in it (at least nothing non-obvious to anyone familiar with the state of the art in, I think, the late 70s) after the law has been changed (by magic, or perhaps by the appointment of supreme court judges favoured by presidents whose campaign funds came from industrialists who wanted software patents).
We all knew the US patent system had become insane some time ago. What none of us outside the US know is why you pretend that your plutocracy is a democracy, claiming that the people rule when in fact only the money rules, as clearly evidenced by the changes in the last few decades to your copyright system and your patent system (amongst many other indicators).


Gosh. I would hope that people know that it's not a Democracy. It's a Republic (well, it's supposed to be... I think "plutocracy" is probably more accurate in this day and age). Even says so right in the "Pledge of Allegiance".

All that aside and whatever you want to call it and whatever problems we may have, I'm pretty happy that I live here. So.... how about you take it just a little easier on my country, please?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1379637
Posted Thursday, November 1, 2012 2:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 17, 2014 6:21 AM
Points: 997, Visits: 6,477
Guys why so serious, and Jeff, I am glad you´re happy living in US, a great country indeed.

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."


Post #1379672
Posted Thursday, November 1, 2012 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 7,736, Visits: 9,482
Jeff Moden (11/1/2012)
Gosh. I would hope that people know that it's not a Democracy. It's a Republic (well, it's supposed to be... I think "plutocracy" is probably more accurate in this day and age). Even says so right in the "Pledge of Allegiance".

Nothing stops it being both a republic and a democracy except that it has become a plutocracy so it isn't a democracy. Half a century ago it was a republican democracy, a genuine democratic republic (have to include the word "genuine" there, as the phrase "democratic republic" without the word "genuine" has been horribly tainted by all the totalitarian governments who called their countries that despite being neither democratic nor a republic) but now it's fast becoming a plutocratic republic.

Of course the words "republican" and "democrat" now have a very specific party-political meaning in the USA and their use in that party-political sense no longer has much (if any) connection with the normal meanings of those words, but their use in that sense isn't designed to mislead as was their use by totalitarians.

All that aside and whatever you want to call it and whatever problems we may have, I'm pretty happy that I live here. So.... how about you take it just a little easier on my country, please?

Oh, I rather like the USA myself. It has a lot of good points, a lot going for it. But I don't take it easy on people/things that I like and care about - that would be an abdication of the obligations of a friend - I speak up when there's something wrong.

So I'll continue to rail against the ongoing drift into plutocracy, the ridiculous extensions of copyright, the contemptible patent system, and the disgraceful excesses of the immigration service - much as I have always railed against the numerous faults in my own country.


Tom
Post #1379774
Posted Thursday, November 1, 2012 6:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 1,021, Visits: 1,711
L' Eomot Inversé (11/1/2012)

Of course the words "republican" and "democrat" now have a very specific party-political meaning in the USA...


Tom, I'm very sorry, but I must correct you. it's "republican" and "democratic" not "democrat" as the party is the "Democratic Party".

Post #1379786
Posted Thursday, November 1, 2012 9:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:18 AM
Points: 31,078, Visits: 15,522
Interesting discussion. Freedman does mention inserts and deletes, but he never wrote on the subject. Perhaps he was mistaken, perhaps he hasn't had time. The KB also mentions it, but it could be standard wording and not specific application to inserts/deletes.

For now, I will stand by Halloween protection being known as applying to updates.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1379887
Posted Thursday, November 1, 2012 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:15 AM
Points: 45, Visits: 341
L' Eomot Inversé (10/31/2012)
This probably a pointless argumnent, because it isn't about anything technical, just about historical and terminological accuracy, and it does appear that quite a few people have adopted historically inaccurate terminology (which is a pretty frequent phenomenon - for example we call Heath's normal form, published in 1971, "Boyce-Codd Normal Form" after a paper published several years later, although the earlier paper was certainly known to the senior of the authors of the later paper). But I'm going to make the argument for historical accuracy (especially since several of the people involved are still alive) anyway (if I were in touch with any of them I would ask them for their views, but unfortunately I'm not).

Well, perhaps there's one thing we can agree on.
Without getting word "from the horses mouth", it's impossible to know for certain what the IBM team considers to be in the domain of the Halloween problem.
All your transcript link provides is a recollection of the circumstances in which it was discovered.
There are a number of unanswered possibilities:
  • When the problem was discovered, did the team consider INSERT / DELETE scenarios at all?
  • Even if they hadn't originally considered the possibility of INSERT / DELETE exhibiting similar unexpected behaviour for the same reason, would they consider those to be a different problem?
  • Did the team consider the Halloween problem as exclusively applicable to the UPDATE statement?
  • Or perhaps even more strictly, did they consider it to be the specific problem of updating Salaries over the Salaries index?
  • When they use the word updates, do they mean changing existing records (UPDATE statement), or any update that changes the state of the database?

I will however, give you something to consider.
Languages, words and names are in continuous evolution to aid communication. Believe me, you're not doing yourself any favours if you reject a logical definition on an historical 'point of order' - regardless of the accuracy of your claim.

Nevertheless, I'm willing to agree to disagree.

L' Eomot Inversé (10/31/2012)

craig 81366 (10/31/2012)

Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME


Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason.

Whereas I can agree to disagree on the previous point, here I must step in and call a spade a spade.
I can only assume your insistence on pursuing this is that you don't fully understand what a spool operation does. It effectively reads all relevant data into temporary storage before proceeding to the next step. This isolates the data from changes that could be effected in said step. It can also be a relatively expensive operation, and so is not nearly as trivial as you seem to think.

Not pulling punches: I consider your claim to be utterly unsubstantiated, unfounded rubbish!
If "blatantly obvious" operations can simply be ignored, where do you draw the line? Are there some hash joins, or nested lookups that are "blatantly obvious"?
The fact is you do not need to spool (first read all data into temporary storage) to calculate an average.

I apologise if you consider the elaborate explanation (below) of how to calculate an average to be beneath you, but please observe that you can perform the calculation as you read data. You do not need to spool it first.
  • Read first record (Val=10)
  • Increment record counter(1), Add Val to cumulative total (0+10=10)
  • Read next record (Val=20)
  • Record Counter=2, Cumulative Total=30
  • Read next record (Val=30)
  • Record Counter=3, Cumulative Total=60
  • Whenever you're done reading records the average is simply Total/Counter


If you think you can deliver a result for a query for AVG without first reading all the records, then think again, because you are horribly wrong.

Your sarcasm is not amusing. Perhaps I should point out that in the above example: the AVG after reading the first record is 10, the second 15, and after reading all the data 20.
Just to be clear, there is a big difference between "calculating the AVG after reading all the data", and "calculating the average as you read the data".
* Do you need to read all the data before you can get the average of all the data - of course!
* Do you need to read all the data before you start doing any calculations - not at all!

You can't deliver the first value of AVG until you have done that. Then, why read the records again?

I will concede that the most practical implementation of the query to DELETE all records less than the average is to precalculate the average, and treat it as a constant input (i.e. spool a single value).
Which is exactly why I elected to provide a second DELETE example in which calculating the decision to DELETE each row is independent of the same calculation for other rows.

Sure, you could track changes in AVG as the table changed, if you wanted to and had an awful lot of compute power to spare,

Not at all, maintaining the average as you delete rows is a very cheap operation when done correctly.
If you have a Cumulative Total and Record Counter (as in the earlier example), then each time you delete a row: decrease the Counter and subtract the Value from the Cumulative Total. The average at any point in time can be delivered probably more cheaply than the IO ops need to actually perform the delete.

In conclusion.
I've found your contributions to this topic to be informative and interesting.
However, I find your flippancy and stubbornness quite annoying. If you're not going to be open-minded enough to actually put thought into what I have to say, I can't be bothered to waste any more time explaining it to you.
I do enjoy a good debate and appreciate logical argument, but presenting unfounded rubbish as fact gets up my nose as much as historical inaccuracies seem to get up yours.

Regards,

Craig
Post #1379991
Posted Thursday, November 1, 2012 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:15 AM
Points: 45, Visits: 341
Steve Jones - SSC Editor (11/1/2012)
Interesting discussion. Freedman does mention inserts and deletes, but he never wrote on the subject. Perhaps he was mistaken, perhaps he hasn't had time. The KB also mentions it, but it could be standard wording and not specific application to inserts/deletes.

For now, I will stand by Halloween protection being known as applying to updates.


Steve, just to clarify.
When you say updates, do you mean the UPDATE statement, or any operation that changes the state of the database?
Post #1379994
Posted Thursday, November 1, 2012 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
I have to agree with Craig on the evolution of terminology.

When a modern American, Englishman, Aussie, et al, says, "decade", he or she most usually does NOT mean "a unit of approximately 10 infantry soldiers and their supporting officers, plus any logistics personnel assigned to them", but most likely means, "a continuous 10 year period, often one starting with a year ending in 1 and ending in a year ending with 0". Same for thousands of Latin and Greek terms that have survived as words but evolved as concepts.

If someone tells you a football team was "decimated" by the other team, you almost certainly don't go out and buy flowers for the widows of the 1:10 members of the decimated team.

Even technical/scientific/mathematical terms get this treatment. If a student at college tells you he's taking a "calculus class", do you assume it's something to do with the substance of rocks?

If your teenage daughter told you she signed up for a gymnastics class, would you immediately call the DA's office to get her teachers prosecuted as pedophiles? (If you know what "gymnasium" originally meant, this will make sense.)

So, claiming that the original people who coined a word have some sort of exclusive right to define it in eternium and ad extemis, seems like a false argument to me. They might have trademark rights on it, if applicable, but eternal exclusive definition control for common usage?


- 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
Post #1380008
Posted Thursday, November 1, 2012 2:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:18 AM
Points: 31,078, Visits: 15,522
I intended UPDATE statement. I know that an update can be a delete/insert at times under the covers, but since I have not found documentation that links this issue to specific places where an INSERT or DELETE command is run, I am leaving this as the UPDATE statement.

If you can find me a reference that shows this problem in the other statements, I'll change it.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1380031
Posted Friday, November 2, 2012 6:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 971, Visits: 2,205


Indeed a nice question, but for me it was "The Beginner's Luck" thing... good it used the only word "modifying..." in the BOL, and just went and selected UPDATE.

(Although I am enjoying the discussion worth all of it... thank you all for their individual contribution.)


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1380327
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse