InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 17:34:38 GMT20http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxSaw those, on my list to read.Thanks, PaulFri, 15 Feb 2013 14:55:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/9/2012)[/b][hr]I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)[/quote]Completed:[url]http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1[/url][url]http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-2[/url][url]http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-3[/url][url]http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-4[/url]Fri, 15 Feb 2013 14:49:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]GSquared (12/10/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (12/10/2012)[/b][hr][quote][b]SQL Kiwi (12/9/2012)[/b][hr]Hi Steve,I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:[url]http://support.microsoft.com/kb/248441[/url][/quote]Thanks, I'll update things.[/quote]Had to laugh when I read this. "I'll update things" in a thread about Halloween Protection. :-D[/quote]I hope he has scheduled the update for October 31 :laugh:Mon, 10 Dec 2012 09:45:03 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Steve Jones - SSC Editor (12/10/2012)[/b][hr][quote][b]SQL Kiwi (12/9/2012)[/b][hr]Hi Steve,I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:[url]http://support.microsoft.com/kb/248441[/url][/quote]Thanks, I'll update things.[/quote]Had to laugh when I read this. "I'll update things" in a thread about Halloween Protection. :-DMon, 10 Dec 2012 09:40:44 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]SQL Kiwi (12/9/2012)[/b][hr]Hi Steve,I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:[url]http://support.microsoft.com/kb/248441[/url][/quote]Thanks, I'll update things.Mon, 10 Dec 2012 09:37:07 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/9/2012)[/b][hr][quote][b]SQL Kiwi (11/6/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (11/1/2012)[/b][hr]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.[/quote]Did you read [url]http://support.microsoft.com/kb/810026[/url]?[/quote]Nope, didn't see that one. So Deletes are documented and known.I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)[/quote]Hi Steve,I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:[url]http://support.microsoft.com/kb/248441[/url]Sun, 09 Dec 2012 04:13:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxCraig, it's clear that we each have our own viewpoint on what a spool operation is and whether all operations carried out by SQL server show up in query plans, and we are not going to agree on those things any more that we are going to agree on what the original meaning of "Halloween Problem" was and whether the adoption by some people of a broad meaniong should has led to a situation where that broad meaning is standard. I didn't want to get into a personal conflict, and I certainly didn't set out to give offence, but it's clear that you were offended by what I wrote, and I have to accept responsability for that effect of what I wrote, so I'm sorry this discussion has turned out as it has, and I apologize for any offense given.Mon, 12 Nov 2012 20:06:07 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]SQL Kiwi (11/6/2012)[/b][hr][quote][b]Steve Jones - SSC Editor (11/1/2012)[/b][hr]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.[/quote]Did you read [url]http://support.microsoft.com/kb/810026[/url]?[/quote]Nope, didn't see that one. So Deletes are documented and known.I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)Fri, 09 Nov 2012 12:17:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (11/7/2012)[/b][hr]use some error reduction technique which is likely to involve something like sorting the numbers (on magnitude, ignoring sign) and eliminating cancelling pairs before doing any arithmetic other than picking up the count to divide by, and then doing the additions in the sorted order (increasing order of magnitude). SQL Server will use the error-ignoring method even when the original data is floating point, which is unfortunate[/quote]I'd like to thank you for those tips on how to reduce cumulative error when you can afford the overhead of an index or sort operation.However, I was under the impression that the primary database platform in our dicusssion is MS SQL Server.And since by your own admission, SQL Server uses a conceptually similar technique to mine, it appears your argument was nothing short of a Red Herring.It could be said that you've been somewhat disingenuous.[quote][b]L' Eomot Inversé (11/7/2012)[/b][hr]and I won't take personal abuse down to that level[/quote]You've already illustrated the level you're capable of taking personal abuse down to.In your previous post (with your obviously well-read, broad and colourful vocabulary) you have already taken personal abuse to a far lower level than the one you now profess to be avoiding.So yet again, you're being completely disingenuous.[quote][b]L' Eomot Inversé (11/7/2012)[/b][hr]But rather than calling you a liar I would call it a mistake you made in the heat of the moment.[/quote]I might have considered the possibility that your false accusation that "I chose to introduce personal abuse" was merely a mistake on your part.However, I find it extremely difficult to believe that someone who possesses the grasp of the English language you clearly have would not understand the significant difference [b]personal abuse[/b] and attacks on utterly unfounded rubbish statements.Furthermore, you seem to be the kind of person who tries to be precise and accurate with vocabulary and terminology.The kind of tangential far fetched leap that's required to label my statements as [b]personal[/b] abuse is quite a long stretch further than: An UPDATE statement might use an eager spool to protect it's operations from the side-effects of rows that have already been updated is similar to a DELETE statement that might need to use an eager spool to protect it's operations from the side-effects of rows that have already been deleted.On the other hand, since you explicitly stated that you're using it as the reason for your personal abuse, I see no other explanation than you deliberately lied to create a fallacious justification to [b]start[/b] personal abuse.I still consider your statement to be an outright blatant lie. (Yes an attack on the [i]statement[/i], [b]not[/b] [i]personal abuse[/i] - but if the shoe fits, you're welcome to wear it.)[quote][b]L' Eomot Inversé (11/7/2012)[/b][hr]So your [quote][b]craig 81366 (11/1/2012)[/b][hr]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.[/quote]is all good standard debate, is it? Some of it looks remarkably like personal abuse to me.[/quote][i]Well, obviously those sentences are riddled with insults and name-calling. If a two-dimensional square looks like three-dimensional sphere, then perhaps it can be considered personal abuse.([b]sarcasm off[/b])[/i]* If you took offence at me describing one of your statements as "unfounded rubbish", might I remind you that you had previously described statements of mine as "nonsense". (You were wrong, but at least you were still being civilised.)* If you took offence at me describing your behaviour as stubborn, then perhaps you'll take heart in hearing that I too am stubborn, but I have no qualms about it:I will stubbornly defend what is right, and stubbornly oppose what is wrong - which is the [b]only reason I'm still replying[/b].Quite frankly, I couldn't care less if you're Babbage, Date, Einstein, Turing, Newton and a hundred other geniuses rolled into one: If you make a rubbish claim, I'm going to challenge you on it. And I'll back the challenge with [b]relevant[/b] and [b]tested/verified[/b] information.You're welcome to rebut my challenge, but just because you discover your claim is indefensible is no cause to get nasty.I'll reiterate, your [i]statement[/i] that "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." is utterly unsubstantiated, unfounded rubbish! [i]There's that significant difference between attacking a flawed statement/argument ([b]as I have done[/b]) versus attacking the person through insults and name-calling ([b]as you have done[/b]).[/i][hr]The following image shows a comparison between two query plans.The only difference between the two queries is that the first uses a self-join to determine which rows to delete, and the other joins to a second table.I ensured that #T2 was identical to #T1, data included.[img]http://www.sqlservercentral.com/Forums/Attachment12680.aspx[/img]If there weren't a spool operation in the first plan, then it would be possible for the DELETE operation to behave differently depending on the order in which the rows are checked for deletion.* Going forwards through the sample data would result in all rows being deleted.* Going backwards through the sample data would result in only the first row being deleted.Clearly the spool operation in the first plan is essential for consistent intuitive and predictable behaviour.I agreed to disagree about whether to call this "Halloween Protection", but [u]you're welcome to suggest another name if you'd like[/u].You claimed that "MS SQL query plan only shows spool operations when they are not blatantly obvious". So:* Is the [b][i]essential[/i][/b] spool operation in the first plan less 'blatantly obvious' than the one you seem to think is in but merely not shown on the second?* Don't you think it's fascinating that the "hidden because it's blantantly obvious" spool in the second plan seems to incur significantly less overhead than the spool in the first plan?What is [b]blatantly obvious[/b] is:* that you never bothered to experiment for yourself with any of the queries I provided.* you never bothered to check, verify or substantiate your utterly rubbish claim.* you expected me to take at face value that your claims were valid and correct based purely on your past experience/work/education/indirect association with System R.And yet you had the cheek and audacity to slander me with:[quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]you are not just an ignoramus but an arrogant and self-important ignoramus[/quote]Perhaps you should take a long hard look in the mirror?On the off-chance that you might, for a change, actually bother to investigate futher than your past recollections, here are all the queries I used to check the above plans. You'll note the inclusion of the AVG query that's been under discussion - you might find its results even more interesting than the ones I've shown.[code="sql"]CREATE TABLE #T1 ( COL1 int, COL2 int)INSERT INTO #T1SELECT 1,2 UNION ALLSELECT 2,3 UNION ALLSELECT 3,4CREATE TABLE #T2 ( COL1 int, COL2 int)INSERT INTO #T2SELECT 1,2 UNION ALLSELECT 2,3 UNION ALLSELECT 3,4DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T1)DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T2)DELETE #T1WHERE COL1 NOT IN (SELECT COL2 FROM #T1)DELETE #T1WHERE COL1 NOT IN (SELECT COL2 FROM #T2)DROP TABLE #T1DROP TABLE #T2[/code]Thu, 08 Nov 2012 13:19:38 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]craig 81366 (11/7/2012)[/b][hr]Only one of us resorts to insults, name-calling and personal abuse to 'make his point'.[/quote]So your [quote][b]craig 81366 (11/1/2012)[/b][hr]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.[/quote]is all good standard debate, is it? Some of it looks remarkably like personal abuse to me. My view is that you started dishing it out and so I started handing it back, so now we are both doing it (which is rather sad) and your "only one" is therefore inaccurate since we are two.[quote][quote][b]L' Eomot Inversé (11/5/2012)[/b][hr](to use the style of personal abuse that you have chosen to introduce into this dialogue)[/quote]Only one of us uses outright blatant lies to justify his "uncivilised" behaviour.[/quote]and the passage I just quoted suggests that the only one using a lie is you, since your "only one" is clearly intended to designate me, and the quoted passage shows you at it. But rather than calling you a liar I would call it a mistake you made in the heat of the moment. Accusing you of "outright blatant lies", whether because I disagree with you or because what you say is plainly untrue or both, would attribute to you a motivation for which there is no evidence - and I won't take personal abuse down to that level (and would have more respect for you if you would refrain from it too).[quote][b]craig 81366 (11/7/2012)[/b][hr][quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]I could carry on by introducing an attack on your method of calculating averages[/quote]Only one of us specifically avoided providing argument against the [b][i]content[/i][/b] of the other's post and instead chose to direct attacks at the other person.[/quote]You are repeating yourself, and just as mistakenly as before. [quote][b]craig 81366 (11/7/2012)[/b][hr][i]By the way, I would be very interested to learn how you intend improving on the mathematically provable minimum number of required addition operations, and a single division operation.[/i][/quote]You perhaps didn't notice that I said that the technique works only under specific circumstances, not that it would never work. The reason it doesn't work under other circumstances is that it makes no attempt to control the cumulative error, which can be very important when there are millions of additions. The specific circumstances in which it works are that there is a datatype capable of representing exactly every datum and every intermediate result (there will probably be error introduced by the division operation even then, but there's no escaping that except by luck and that is in any case a small rounding error introduced by a single divide op). So, for example, if you have 10 million numbers with an average value of about 10^29 you can't do it because the sum will overflow numeric(38,0), bigint, and any other exact numeric type SQL or most other languages have - you have to use FLOAT to do the arithmetic. So now you are into inexact numerics, and have introduced representation errors as SQL uses the wrong floating point base for accurate representation of our usual decimal number system, so you don't want to introduce yet more errors, and you will use some error reduction technique which is likely to involve something like sorting the numbers (on magnitude, ignoring sign) and eliminating cancelling pairs before doing any arithmetic other than picking up the count to divide by, and then doing the additions in the sorted order (increasing order of magnitude). SQL Server will use the error-ignoring method even when the original data is floating point, which is unfortunate and is why some scientific and engineering projects either pull the data out of the database to compute aggregates, instead of using the built-in capability, or write their own DBMS that doesn't have the problem.[quote][b]craig 81366 (11/7/2012)[/b][hr][quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]I could be even nastier with your method of recalculating the average as elements are deleted, ... to be done by the method you suggest [/quote]Only one of us deliberately misrepresents the other's statements in order easily attack them. (See [url=http://en.wikipedia.org/wiki/Straw_man]http://en.wikipedia.org/wiki/Straw_man[/url])[i]To reiterate, the only reason I presented the technique was to illustrate your error in assuming such a calculation would need "an awful lot of compute power to spare".[/i][/quote]And the technique doesn't work because, again, it does no error reduction when it has to deal with inexact numerics. Of course it would work any time computing the average using the no-error-avoidance worked because everything could be kept exact and would still have worked if the data had been presented in the inverse order to its subsequent removal. But when inexact arithmetic is needed, or when the original data is held in an inexact format, unless the values are removed in the inverse order to that in which they were added to the total when calculating the average of all vales, it could lead to very inaccurate results indeed. I don't think anything I said misrepresented anything you wrote. We both know perfectly well that MS doesn't use that technique for calculating a modified average, because they don't compute modified averages at all. If one wanted to compute them with reasonably small errors then a lot of resource would be required, because after some threshold of values had been removed one would need to compute the average from scratch to stop accumulating error, and that computation involves (as we are looking at inexact data) a sort of all the (remaining) data. So my statement that an awful lot of compute power would be required was perfectly correct (unless one arbitrarily refuses to take account of the need to do error reduction when handling inexact values). I don't think my comments about the error-accumulation potential of the method were in any way misrepresenting what you said, in fact I can't imagine how you could interpret them as doing so, and at first I took your use of the word "deliberately" in that context as an intentionally vicious unjustifiable slur. On cooler reflection, I shall just regard it as another unfortunate mistake in the heat of the moment. [quote][b]craig 81366 (11/7/2012)[/b][hr]Only one of us resorted to quoting his education background, experience and work history in an effort to give credibility to his argument.[/quote]I described some work background because it is clearly relevant to whether I'm aware that producing and using a spool can be an expensive operation - something you had claimed, with no apparent justification at all, that I clearly didn't understand. I mentioned some education background to indicate how old the spool concept is - since you appeared to think it was something newly invented for databases at some time in the 70s.[quote]Only one of us has made comments suggesting an ageist prejudice.[/quote]I must have missed that one somehow - which message was it in?[quote]Only one of us has made statements insinuating the other is "stupid".[/quote]Well, as far as I can see neither of us has actually applied the word "stupid" to the other. Several things you have said clearly suggest (and have clearly been intended to suggest) that I'm an ignorant and arrogant person who pays no attention to other people's arguments, and as I would regard anyone who displayed those qualities as utterly stupid that I think is the nearest either of us has come to insinuating stupidity. [quote]You're right: Only one of us chose not to "mix the civilised part of this exchange with the uncilvilised part"; but then again, only one of us chose to write an "uncivilised" post at all.[/quote]Well, I disagree. You posted a comment with some very offensive and uncivilised remarks in it, and I then responded in kind. Unlike you I recognised that I was being uncivilised (although I don't think I ever got down quite to your level - that "deliberately misrepresents" really is the pits, "not going to be open-minded enough to actually put thought into what I have to say" was almost as bad, and "outright blatant lies" is extremely nasty too - you won't find anything as offensive as any of those in my comments.Anyway, I'm convinced this isn't the proper forum for a flame war, so I don't think I'll particpate in any more of this silliness.Wed, 07 Nov 2012 17:04:26 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]Up to now, nothing nasty. I'll have to address the rest of your post in a separate message, because I don't want to mix the civilised part of this exchange with the uncilvilised part (and that, incidentally, illustrates one of the differences between us).[/quote]There are many differences between us...[quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]you are (...) an ignoramus[/quote][quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]you are not just an ignoramus but an arrogant and self-important ignoramus[/quote]Only one of us resorts to insults, name-calling and personal abuse to 'make his point'.[quote][b]L' Eomot Inversé (11/5/2012)[/b][hr](to use the style of personal abuse that you have chosen to introduce into this dialogue)[/quote]Only one of us uses outright blatant lies to justify his "uncivilised" behaviour.[quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]I could carry on by introducing an attack on your method of calculating averages[/quote]Only one of us specifically avoided providing argument against the [b][i]content[/i][/b] of the other's post and instead chose to direct attacks at the other person.[i]By the way, I would be very interested to learn how you intend improving on the mathematically provable minimum number of required addition operations, and a single division operation.[/i][quote][b]L' Eomot Inversé (11/5/2012)[/b][hr]I could be even nastier with your method of recalculating the average as elements are deleted, ... to be done by the method you suggest [/quote]Only one of us deliberately misrepresents the other's statements in order easily attack them. (See [url=http://en.wikipedia.org/wiki/Straw_man]http://en.wikipedia.org/wiki/Straw_man[/url])[i]To reiterate, the only reason I presented the technique was to illustrate your error in assuming such a calculation would need "an awful lot of compute power to spare".[/i]Futhermore:Only one of us resorted to quoting his education background, experience and work history in an effort to give credibility to his argument.Only one of us has made comments suggesting an ageist prejudice.Only one of us has made statements insinuating the other is "stupid".You're right: Only one of us chose not to "mix the civilised part of this exchange with the uncilvilised part"; but then again, only one of us chose to write an "uncivilised" post at all.Wed, 07 Nov 2012 11:34:29 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/1/2012)[/b][hr]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.[/quote]Did you read [url]http://support.microsoft.com/kb/810026[/url]?Tue, 06 Nov 2012 02:05:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]craig 81366 (11/1/2012)[/b][hr][quote][b]L' Eomot Inversé (10/31/2012)[/b][hr][quote][b]craig 81366 (10/31/2012)[/b][hr]Then why when I run the following by itself, does the plan [b]not[/b] spool?[code="sql"]SELECT AVG(COL) FROM TABLE_NAME[/code][/quote]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.[/quote]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. Not pulling punches: I consider your claim to be utterly unsubstantiated, unfounded rubbish![/quote]There were "spool operations" way back in ancient times, many years before I got involved in computing (I started computing back 1963 at Oxford with some of Lucius Fox's numerical methods stuff, then some more odd bits in 1964 at STC, worked seriously on it in 1966 at RHEL, played with it while a research student at Bristol U in 1966/67, but only switched from being a mathematician to being a computer scientist some time after I joined NRL at the end of that academic year). What a spool operation did then - and still does now - is read some data from permanent store or working store, execute some transformations on it (the transformations may be null) and place the result or those transformations in some temporary store which is preserved until all subsequent already planned operations requiring the transformed form of that data are completed, at which point that transformed data (usually called "the spool") is discarded. This is a general computing concept dating from the 1950s, long before the existence of any RDBMS. The idea that term "spool operation" has changed its meaning just to cope with RDBMS is complete insanity, since the term is still used every day in connection with passing data to networked destinations (eg to a networked printer). The purposes of spooling are at least threefold: (i) to avoid unwanted repeat computation; (ii) as a direct consequence of (i), to ensure that a single value (the spooled value) of the (derived) data is used for all dependent tasks; and (iii) to allow use to be made of the spool even though the original data may be (temporarily) inaccessible (probably irrelevant today, but enormously important when I first worked in data communications). It is of course irrelevant whether the only use of the spool before destroying it is to format it and output to a console (particularly when that output is asynchronous). If you have a different understanding of "spool", then you are just plain wrong - in fact if you want to disagree with that definition then you are (to use the style of personal abuse that you have chosen to introduce into this dialogue) an ignoramus, and your insistence that your utter misunderstanding of the term is correct while my accurate understanding is incorrect is a very clear indication that you are not just an ignoramus but an arrogant and self-important ignoramus. Of course all of us, not just MS, normally ignore the trivial spool where output is to a single console which just happens to be active when the spool is created and has a reasonably close to synchronous (albeit asynchronous) connection to the task creating the spool.[quote]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.[/quote]It doesn't read all relevant data and put it into temporary storage. It reads all relevant data and puts some value computed from it (perhaps as little as 1 bit computed from a terabyte of original data) into temporary storage. It matters not one jot (just to correct something else you got wrong elsewhere, and save me the trouble of quoting it) whether it computes the derived data after reading all the original data or computes it on the fly.As for being expensive and not as trivial as I think, I should perhaps give you some of my history: in the late 80s I was chief architect of a declarative system R&D project that involved a relational (but definitely NOT SQL) database. One of the concepts being pushed around was that the DML/DDL should be declarative except at transaction boundaries (ie transactions should never see their own afterlooks); I killed that precisely because I understood the cost of spooling. Single statements we could afford (maybe - but if not, we didn't have a viable project); whole multi-statement transactions, no bl***y way! I think that that suggests I understood the cost a few decades ago. Maybe you think I've forgotten?[quote]The fact is you do not need to spool (first read all data into temporary storage) to calculate an average.[/quote]Ye, you do. It doesn't all have to be there simultaneously, of course. Nobody but you has sugested that for an average one spools the original data, rather than the single datum the average.I could carry on by introducing an attack on your method of calculating averages (which any mathematician knows doesn't work unless you have exact numerics with adequate accuracy throughout) but I won't as MS SqQL seems to do it that way. I could be even nastier with your method of recalculating the average as elements are deleted, but I don't see the point in doing so - after all, it is utterly unimaginable that MS is stupid enough to allow the on-the-fly correction as elements are deleted to be done by the method you suggest (if I discovered that it did I'd be recommending to HMG to instruct its suppliers to ban the use of aggregates in SQL Server in any software it commissions except where the ranges and the data types are such that the calculation is provably safe, and I believe that there are enough mathematically qualified civil servants that my recommendation would stick; and it would probably spread to US Gov too; but it won't happen, because MS is not that stupid; in fact it uses what you want to miscall "Halloween Protection" to ensure that that never happens).Mon, 05 Nov 2012 19:00:21 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]craig 81366 (11/1/2012)[/b][hr]All your transcript link provides is a recollection of the circumstances in which it was discovered.There are a number of unanswered possibilities:[ul][li]When the problem was discovered, did the team consider INSERT / DELETE scenarios at all?[/li][li]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?[/li][li]Did the team consider the Halloween problem as exclusively applicable to the UPDATE statement?[/li][li]Or perhaps even more strictly, did they consider it to be the specific problem of updating Salaries over the Salaries index?[/li][li]When they use the word [i]updates[/i], do they mean changing existing records (UPDATE statement), or any [i]update[/i] that changes the state of the database?[/li][/ul][/quote]When I read what the team published, something over 30 years ago, if I recall it correctly, it was about an update being repeated several times on a row, instead of once. Yes, they looked for the underlying cause, but the problem they addressed was the repeated update. Unfortunately I can't recall the reference - there were rather a lot of papers being published in those days, and it's not easy to remember which journal and with what title everything appeared in. But the System R tyeam published very freely in those days (although they did later see the beginnings of the later "keep it all secret until we have a patent" frenzy) and they published that information quite quickly. You may have seen some earlier comments of mine on this site about the relationship of the IBM library with NRL in the 1960s (I was at the NRL end, while NRL still existed) and that free exchange of information carried on well into the 70s.[quote]I will however, give you something to consider.Languages, words and names are in continuous evolution to [i]aid communication[/i]. 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.[/quote]It's not a "point of order". It's about what people mean when they use that term. What makes you believe that you youngsters own the meaning, rather than us boring grey-haired old ****s?[quote]Nevertheless, I'm willing to agree to disagree.[/quote]OK, so am I. We disagree. That means only one of us can be right, and I know which one it is - while, I believe, you don't.Up to now, nothing nasty. I'll have to address the rest of your post in a separate message, because I don't want to mix the civilised part of this exchange with the uncilvilised part (and that, incidentally, illustrates one of the differences between us).Mon, 05 Nov 2012 17:30:02 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxNice interesting question, thanks for sharing. Learn something new...Mon, 05 Nov 2012 02:27:44 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx:-)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. :hehe:(Although I am enjoying the discussion worth all of it... thank you all for their individual contribution.)Fri, 02 Nov 2012 06:40:10 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxI 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.Thu, 01 Nov 2012 14:40:21 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxI 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?Thu, 01 Nov 2012 13:45:20 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/1/2012)[/b][hr]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.[/quote]Steve, just to clarify.When you say [i]updates[/i], do you mean the UPDATE statement, or any operation that changes the state of the database? :-)Thu, 01 Nov 2012 13:22:07 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (10/31/2012)[/b][hr]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).[/quote]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:[ul][li]When the problem was discovered, did the team consider INSERT / DELETE scenarios at all?[/li][li]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?[/li][li]Did the team consider the Halloween problem as exclusively applicable to the UPDATE statement?[/li][li]Or perhaps even more strictly, did they consider it to be the specific problem of updating Salaries over the Salaries index?[/li][li]When they use the word [i]updates[/i], do they mean changing existing records (UPDATE statement), or any [i]update[/i] that changes the state of the database?[/li][/ul]I will however, give you something to consider.Languages, words and names are in continuous evolution to [i]aid communication[/i]. 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.[quote][b]L' Eomot Inversé (10/31/2012)[/b][hr][quote][b]craig 81366 (10/31/2012)[/b][hr]Then why when I run the following by itself, does the plan [b]not[/b] spool?[code="sql"]SELECT AVG(COL) FROM TABLE_NAME[/code][/quote]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.[/quote]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 [b]not need to[/b] 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 [b]do not[/b] need to spool it first.[ul][li]Read first record (Val=10)[/li][li]Increment record counter(1), Add Val to cumulative total (0+10=10)[/li][li]Read next record (Val=20)[/li][li]Record Counter=2, Cumulative Total=30[/li][li]Read next record (Val=30)[/li][li]Record Counter=3, Cumulative Total=60[/li][li]Whenever you're done reading records the average is simply Total/Counter[/li][/ul][quote]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.[/quote]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 [i]after[/i] reading all the data", and "calculating the average [i]as you read[/i] the data".* Do you need to read all the data before you can get the average of all the data - [b]of course[/b]!* Do you need to read [u]all[/u] the data before you start doing any calculations - [b]not at all[/b]![quote]You can't deliver the first value of AVG until you have done that. Then, why read the records again?[/quote]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.[quote]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,[/quote]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,CraigThu, 01 Nov 2012 13:19:44 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxInteresting 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.Thu, 01 Nov 2012 09:30:04 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (11/1/2012)[/b][hr]Of course the words "republican" and "democrat" now have a very specific party-political meaning in the USA...[/quote]Tom, I'm very sorry, but I must correct you. it's "republican" and "democratic" not "democrat" as the party is the "Democratic Party".Thu, 01 Nov 2012 06:53:35 GMTvenoymhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Jeff Moden (11/1/2012)[/b][hr]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".[/quote]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.[quote]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?[/quote]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.Thu, 01 Nov 2012 06:20:11 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxGuys why so serious, and Jeff, I am glad you´re happy living in US, a great country indeed.Thu, 01 Nov 2012 02:39:35 GMTD.Ochttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (10/31/2012)[/b][hr][quote][b]Meow Now (10/31/2012)[/b][hr]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). [url=http://www.google.com/patents/US6122644]http://www.google.com/patents/US6122644[/url][/quote]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).[/quote]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?Thu, 01 Nov 2012 00:07:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]Meow Now (10/31/2012)[/b][hr]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). [url=http://www.google.com/patents/US6122644]http://www.google.com/patents/US6122644[/url][/quote]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).Wed, 31 Oct 2012 22:05:49 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxThis 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).[quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]L' Eomot Inversé (10/31/2012)[/b][hr]Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.[/quote]Really?Then why when I run the following by itself, does the plan [b]not[/b] spool?[code="sql"]SELECT AVG(COL) FROM TABLE_NAME[/code][/quote]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. 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. You can't deliver the first value of AVG until you have done that. Then, why read the records again? 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, but poeple neither wanted to nor had that power to spare so in the early versions of SQL (or of SEQUEL, or whatever it was called before it became SEQUEL) AVG was not recomputed. It isn't today, not because some additional spool operation, beyond what is required to compute AVG in the first place, is introduced, but because AVG has to be computed.[quote] An average [i]can[/i] be calculated and maintained incrementally without spooling data.[/quote]It can't be computed for the first time without a spool (ie reading all the input before going to the next step).[quote][quote]... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is [url=http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html#Index197][i]here[/i][/url].[/quote]Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.However, I didn't see anything that claimed the Halloween Problem was considered [i]exclusive[/i] to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."[/quote]So "something that's "a sort of variation on that" is an instance of "that"? YG&LF, or perhaps GHU - what else can I say? Why did she say "a sort of variation on that" rather than just "another instance of that" or "another example of that", I wonder? Perhaps she though the various possible phrases had different interpretations, and chose one which can't reasonably nbe taken to mean what you want it to mean precisely because what you want it to mean is not what she intended to say? I don't know, I wasn't there at that meeting, but I'm not the one trying to attribute to her a meaning which what she actually said cleafrly doesn't imply. [quote]In contrast to your historical analogy, consider this:If someone were to observe a ball and decide: [i]Due to your properties of "roundedness", I dub thee sphere.[/i]And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?[/quote]So all spheroids are spheres, are they? They are all round - any corners would make them not spheroids. Even on a technical, as opposed to historical, basis your argument doesn't fly. Of course if in your sphere example the properties of roundness concerned require both that there be a single focus and that all diameters be the same (either of those is a consequence of the other in some geometries, but not in all) then yes, the new thing would be a sphere. That's rather a big and nasty requirement on the properties, I guess the equivalent condition wrt the Halloween Problem would be that it involved an insert statement, so you are seem to be comparing tautologies with contradictions - not usually a useful passtime.Wed, 31 Oct 2012 21:33:50 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxI 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). [url=http://www.google.com/patents/US6122644]http://www.google.com/patents/US6122644[/url]Further down the doc there is a section that states:38. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is an insert operator.39. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is a delete operatorHere is also a KB article that specifically indicates that an INSERT, UPDATE, or DELETE statement may need Halloween protection. [url=http://support.microsoft.com/kb/294860]http://support.microsoft.com/kb/294860[/url]Wed, 31 Oct 2012 13:22:32 GMTMeow Nowhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxWell, Happy Halloween!!!Wed, 31 Oct 2012 12:11:03 GMTD.Ochttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]L' Eomot Inversé (10/31/2012)[/b][hr][quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]GilaMonster (10/31/2012)[/b][hr][quote][b]Andrew Diniz (10/31/2012)[/b][hr]After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:[/quote]No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.[/quote]I beg to differ.[/quote]You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.[quote]You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.[/quote]Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.[/quote]Really?Then why when I run the following by itself, does the plan [b]not[/b] spool?[code="sql"]SELECT AVG(COL) FROM TABLE_NAME[/code]An average [i]can[/i] be calculated and maintained incrementally without spooling data.[quote]... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is [url=http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html#Index197][i]here[/i][/url].[/quote]Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.However, I didn't see anything that claimed the Halloween Problem was considered [i]exclusive[/i] to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."In a nutshell, the problem boils down to: a data operation is expected to behave in a predetermined way, but is not due to a side effect of its work unexpectedly affecting its own behaviour recursively.[quote]I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.[/quote]In contrast to your historical analogy, consider this:If someone were to observe a ball and decide: [i]Due to your properties of "roundedness", I dub thee sphere.[/i]And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?Wed, 31 Oct 2012 09:30:07 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxAll, have a scary Halloween!Steve, thanks for the question!Wed, 31 Oct 2012 09:07:44 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]GilaMonster (10/31/2012)[/b][hr][quote][b]Andrew Diniz (10/31/2012)[/b][hr]After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:[/quote]No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.[/quote]I beg to differ.[/quote]You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.[quote]Consider a DELETE statement of the form:[code="sql"]DELETE TABLE_NAMEWHERE COL < CONST[/code]as opposed to [code="sql"]DELETE TABLE_NAMEWHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)[/code]You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.[/quote]Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else. It is of course rather a good thing that the spool just happens to ensure that the Halloween Problem can't arise, so that no Halloween protection is needed in this case. One side effect of that good thing is that that 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only, because the delete issue didn't exist; and the Halloween Problem is the name that they gave to the problem they discussed that day, and that is a problem with update only, and Halloween Protectiopn means protectin from that update only problem, not anything else. At the 1995 System R/SQL reunion surviving members of the team talked about the early development of the language now called SQL, and Pat Selinger (she may still have been Pat Griffiths when she and Morton Astrahan identified this problem, or maybe she was already married by then) gave a description of this particular thing. Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available as an SRC technical note, which is currently accessible at his wesite and (in a Russian version) at CITForum.ru. The relevant passage is [url=http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html#Index197][i]here[/i][/url].I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.Wed, 31 Oct 2012 08:35:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxHappy Halloween AllWed, 31 Oct 2012 08:22:52 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxNice interesting question Steve:-)Wed, 31 Oct 2012 06:53:03 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]bitbucket-25253 (10/30/2012)[/b][hr]Hmm caught me on that one with Halloween coming up in the USA tomorrow thought this was one of those joke questions ... OH well so be it[/quote]Same here... I tried to think of what statement I'd want to protect against the most... and it was a toss up between update and delete.... sadly I picked the wrong one.Wed, 31 Oct 2012 06:51:49 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]vk-kirov (10/31/2012)[/b][hr][quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]GilaMonster (10/31/2012)[/b][hr]You can't insert or delete a row multiple times[/quote]I beg to differ...that spool is essential to protecting against deleting more rows than expected[/quote]"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.[/quote]It seems to me that you're quibbling about how a questionable statement by GilaMonster differs from mine.Perhaps take a look at the very last sentence of Craig Freedman's (not me) blog post:[url]http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx[/url]He says he's been working for the SQL Server team for quite a few years, so probably can be considered authoritative on the matter. ;-)Edit: To clarify... it's not the statement "cannot delete a row multiple times" that I consider questionable. (Obviously :-P) It's questionable to use that as the explanation why DELETE's don't apply to the Halloween Problem.Wed, 31 Oct 2012 06:30:18 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspxThanks for the great question. Had to do a little reading to get the answer.Wed, 31 Oct 2012 06:29:54 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]GilaMonster (10/31/2012)[/b][hr][quote][b]Andrew Diniz (10/31/2012)[/b][hr]After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:[/quote]No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.[/quote]This is a common misconception. Internally, all data-changing operations are referred to as updates. This includes insert, delete, update, and merge. Confusing for SQL people perhaps, but that's the way it is. This usage in Ian's post seems to have misled Steve today because Halloween Protection is most definitely required (and a strategy computed) for update plans (I/U/D/M) in general, not just updates. There were several bugs (in SQL Server 7/2000 I think) that related to incorrect HP for inserts and deletes with self-joins, for example.Craig Freedman states this explicitly at the end of the following entry:[url]http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx[/url]edit: Adding KB [url]http://support.microsoft.com/kb/810026[/url]Wed, 31 Oct 2012 06:19:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]vk-kirov (10/31/2012)[/b][hr][quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]GilaMonster (10/31/2012)[/b][hr]You can't insert or delete a row multiple times[/quote]I beg to differ...that spool is essential to protecting against deleting more rows than expected[/quote]"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.[/quote]Yeah, it is all about using the exact SAME row more than once... If you INSERT the 'same' row more than once, it becomes 2 different rows in the database. If you DELETE the 'same' row more than once, you are essentially only deleting it once... Once the row is deleted, it is deleted :-)Wed, 31 Oct 2012 06:15:15 GMTSchalk.Harmsenhttp://www.sqlservercentral.com/Forums/Topic1379132-32-1.aspx[quote][b]craig 81366 (10/31/2012)[/b][hr][quote][b]GilaMonster (10/31/2012)[/b][hr]You can't insert or delete a row multiple times[/quote]I beg to differ...that spool is essential to protecting against deleting more rows than expected[/quote]"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.Wed, 31 Oct 2012 05:37:25 GMTvk-kirov