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 Monday, November 05, 2012 2:27 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: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
Nice interesting question, thanks for sharing. Learn something new...

Thanks
Post #1380929
Posted Monday, November 05, 2012 5:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
craig 81366 (11/1/2012)
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?


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.
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.

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?
Nevertheless, I'm willing to agree to disagree.
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).


Tom
Post #1381324
Posted Monday, November 05, 2012 7:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
craig 81366 (11/1/2012)
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.

Not pulling punches: I consider your claim to be utterly unsubstantiated, unfounded rubbish!

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.
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.

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?
The fact is you do not need to spool (first read all data into temporary storage) to calculate an average.

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).


Tom
Post #1381340
Posted Tuesday, November 06, 2012 2:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
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.

Did you read http://support.microsoft.com/kb/810026?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1381451
Posted Wednesday, November 07, 2012 11:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:14 AM
Points: 41, Visits: 288
L' Eomot Inversé (11/5/2012)
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).

There are many differences between us...

L' Eomot Inversé (11/5/2012)
you are (...) an ignoramus

L' Eomot Inversé (11/5/2012)
you are not just an ignoramus but an arrogant and self-important ignoramus

Only one of us resorts to insults, name-calling and personal abuse to 'make his point'.

L' Eomot Inversé (11/5/2012)
(to use the style of personal abuse that you have chosen to introduce into this dialogue)

Only one of us uses outright blatant lies to justify his "uncivilised" behaviour.

L' Eomot Inversé (11/5/2012)
I could carry on by introducing an attack on your method of calculating averages

Only one of us specifically avoided providing argument against the content of the other's post and instead chose to direct attacks at the other person.
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.

L' Eomot Inversé (11/5/2012)
I could be even nastier with your method of recalculating the average as elements are deleted, ... to be done by the method you suggest

Only one of us deliberately misrepresents the other's statements in order easily attack them. (See http://en.wikipedia.org/wiki/Straw_man)
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".

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.
Post #1382104
Posted Wednesday, November 07, 2012 5:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
craig 81366 (11/7/2012)
Only one of us resorts to insults, name-calling and personal abuse to 'make his point'.
So your
craig 81366 (11/1/2012)
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.

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.
L' Eomot Inversé (11/5/2012)
(to use the style of personal abuse that you have chosen to introduce into this dialogue)

Only one of us uses outright blatant lies to justify his "uncivilised" behaviour.
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).
craig 81366 (11/7/2012)

L' Eomot Inversé (11/5/2012)
I could carry on by introducing an attack on your method of calculating averages

Only one of us specifically avoided providing argument against the content of the other's post and instead chose to direct attacks at the other person.
You are repeating yourself, and just as mistakenly as before.
craig 81366 (11/7/2012)
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.

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.
craig 81366 (11/7/2012)
L' Eomot Inversé (11/5/2012)
I could be even nastier with your method of recalculating the average as elements are deleted, ... to be done by the method you suggest

Only one of us deliberately misrepresents the other's statements in order easily attack them. (See http://en.wikipedia.org/wiki/Straw_man)
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".

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.
craig 81366 (11/7/2012)
Only one of us resorted to quoting his education background, experience and work history in an effort to give credibility to his argument.
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.
Only one of us has made comments suggesting an ageist prejudice.
I must have missed that one somehow - which message was it in?
Only one of us has made statements insinuating the other is "stupid".
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.
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.
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.


Tom
Post #1382218
Posted Thursday, November 08, 2012 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:14 AM
Points: 41, Visits: 288
L' Eomot Inversé (11/7/2012)
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

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.

L' Eomot Inversé (11/7/2012)
and I won't take personal abuse down to that level

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.

L' Eomot Inversé (11/7/2012)
But rather than calling you a liar I would call it a mistake you made in the heat of the moment.

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 personal abuse 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 personal 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 start personal abuse.
I still consider your statement to be an outright blatant lie. (Yes an attack on the statement, not personal abuse - but if the shoe fits, you're welcome to wear it.)

L' Eomot Inversé (11/7/2012)
So your
craig 81366 (11/1/2012)
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.

is all good standard debate, is it? Some of it looks remarkably like personal abuse to me.

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.(sarcasm off)
* 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 only reason I'm still replying.

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 relevant and tested/verified 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 statement 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! There's that significant difference between attacking a flawed statement/argument (as I have done) versus attacking the person through insults and name-calling (as you have done).


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.



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 you're welcome to suggest another name if you'd like.

You claimed that "MS SQL query plan only shows spool operations when they are not blatantly obvious". So:
* Is the essential 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 blatantly obvious 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:
L' Eomot Inversé (11/5/2012)
you are not just an ignoramus but an arrogant and self-important ignoramus

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.

CREATE TABLE #T1 (
COL1 int,
COL2 int
)
INSERT INTO #T1
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4

CREATE TABLE #T2 (
COL1 int,
COL2 int
)
INSERT INTO #T2
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4

DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T1)
DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T2)

DELETE #T1
WHERE COL1 NOT IN (SELECT COL2 FROM #T1)
DELETE #T1
WHERE COL1 NOT IN (SELECT COL2 FROM #T2)

DROP TABLE #T1
DROP TABLE #T2



  Post Attachments 
EagerSpoolDelete.png (99 views, 51.52 KB)
Post #1382690
Posted Friday, November 09, 2012 12:17 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:11 PM
Points: 32,810, Visits: 14,959
SQL Kiwi (11/6/2012)
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.

Did you read http://support.microsoft.com/kb/810026?


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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383197
Posted Monday, November 12, 2012 8:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 8,289, Visits: 8,742
Craig, 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.

Tom
Post #1383927
Posted Sunday, December 09, 2012 4:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
Steve Jones - SSC Editor (11/9/2012)
SQL Kiwi (11/6/2012)
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.

Did you read http://support.microsoft.com/kb/810026?


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.

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:

http://support.microsoft.com/kb/248441




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1394393
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse