UPdate statement

Question

UPdate statement

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

rbarryyoung (9/3/2008)
Because I put it in.

That's why you keep seeing the old "the order of processing is not guaranteed" disclaimers on MSDN as to these kinds of things. Depending on the disk system you're putting it on, it's just as likely to be reading forward as it might be of reading it "on the way back".

That being said - by BOL's definition of UPDATE - the value that updated the row is the "first" one found, since

a single UPDATE statement never updates the same row twice.

(BOL)

So - once a row is updated as part of this UPDATE, any further values that are detected that might have affected that same row are ignored.

In other words, even with small tables, there is zero guarantee that it will pick first/last/ whatever, unless you force the issue somehow (either by "breaking the tie" with a ROW_NUMBER(), or perhaps with a WITH (INDEX(ClusteredIndexName)) construct, giving a distinct, predictable processing order).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Matt Miller (9/3/2008)
rbarryyoung (9/3/2008)
Because I put it in.
That's why you keep seeing the old "the order of processing is not guaranteed" disclaimers on MSDN as to these kinds of things. Depending on the disk system you're putting it on, it's just as likely to be reading forward as it might be of reading it "on the way back".

Agreed. I think that's what I've been saying for the last two pages. 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

rbarryyoung (9/3/2008)
Matt Miller (9/3/2008)
rbarryyoung (9/3/2008)
Because I put it in.
That's why you keep seeing the old "the order of processing is not guaranteed" disclaimers on MSDN as to these kinds of things. Depending on the disk system you're putting it on, it's just as likely to be reading forward as it might be of reading it "on the way back".
Agreed. I think that's what I've been saying for the last two pages. 😛

Yup. These kinds of discussions get very "who's on first" rather quickly...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?