Jeff Moden (11/27/2008)
Lynn Pettis (11/27/2008)
Okay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.Hugo is putting out a lot of "what ifs" and he and I have already had this argument on the "Running Total" article. He hasn't been able to make your code or my code or any properly written running total code using the "quirky update" break. If you add the correct index hint to your code, he won't be able to break that, either.
Hi Jeff,
All my "what ifs" are actually dependencies on undocumented behaviour you take. Fine if you choose to take those risks, but if you're going to encourage others to do so, without adding a waring in large red letters that the behaviour may change without notice, then I'm going to add that warning.
I just posted code that shows that a SELECT query with the index hints you recommend will not always return rows in sorted order. I'll admit that I did not (yet) break the update. More on that below.
I think that people are way too paranoid about undocumented features, especially this one. In order for them to release a service pack that would destroy the ability of the "quirky" update to work, they would have to rewrite half the engine to change the way clustered indexes work behind the scenes and how Update works behind the scenes.
The only reason I could not break the update is that the query optimizer creates a plan that forces an ordered clustered index scan. And I don't know about you, but I fail to see why it doesn't allow an unordered scan. Probably because some details of how exactly the update is processed internally. Details that might be changed - and that would not require rewriting half the engine.
The way clustered indexes work and the way Update works with indexes are both fully documented. Putting 2+2 together, is apparently not.
The way UPDATE works with variables in the SET clause is also documented. I never realised this before, but I just saw it when I was checking some other info. Here's the quote from Books Online for SQL Server 2005 (sept 2007 update). You can find it in the page about the UPDATE statement:
"Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause."
The other thing is that using ONLY fully documented features is no guarantee that you're not going to have to rewrite code. For example, in SQL Server 2000 SP3a, it was fully documented as to what privs you needed to use the wonderful sp_MakeWebTask extended store procedure. SP4 came out and suddenly you needed SA privs to use it... a change to a fully documented procedure broke a lot of people's code.
Agree. This is not Microsoft's usual style, but they sure emssed up here.
Here's another fully documented feature right straight out of BOL...
(...)
[font="Arial Black"]column_alias = expression [/font]
(...)
... yet the boogers at Microsoft have deprecated it.
They did? Hmm, that must have gone past me. Where is this documented? (Not that I mind much, I personally always preferred the "expression AS alias" version).
However, this actually just proves my point. Of course changes will be made. But the normal path for changes to documented features is to announce deprecation without removing support for one or two versions, then continuing to support it in backward compatibility mode only for one or two more versions. This will give you an advance warning, and plenty time to replace the code. Not so with changes to undocumented features - they can (and will!) be changed without notice. Remember views with TOP 100 PERCENT ... ORDER BY in SQL Server 2000, that stopped working in SQL Server 2005? Or GROUP BY always returning ordered results, even without ORDER BY, in SQL Server 6.5?
If you follow the best practice of testing service packs on non production machines before you put it into production, you'll never get caught with your pants down.
Except that some behaviour will only occur under heavy load. I had to go to great lengths to cause pages to be allocated out of order in my repro code, and I only did so because I knew that this would break your code. Low-use test systems will not usually get such allocations - but heavily used production systems might.
Heh... and until they either break the hell out of the engine or someone comes up with a better and faster way to do running totals, my recommendation would be to continue to use the "quirky" update to do them.
And my recommendation would be to calculate them on the client :D. And if that is really impossible, then use either a cursor or a set-based query with a correlated subquery, depending on the data distribution.