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 ««12

UPDATE SELECT Expand / Collapse
Author
Message
Posted Wednesday, February 8, 2012 8:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:11 AM
Points: 1,851, Visits: 21,646
The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537



Post #1249018
Posted Wednesday, February 8, 2012 8:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Mark-101232 (2/8/2012)
The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

Who is this question directed at Mark?




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1249025
Posted Wednesday, February 8, 2012 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:11 AM
Points: 1,851, Visits: 21,646
SQL Kiwi (2/8/2012)
Mark-101232 (2/8/2012)
The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

Who is this question directed at Mark?


Ha! It's not directed at any one person, just a general point.


____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537



Post #1249033
Posted Wednesday, February 8, 2012 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:10 AM
Points: 5,820, Visits: 11,335
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.

Fully documented - maybe, although a quick search just now didn't reveal anything. Well known - I'd dispute that. I think there are a lot of people who use it (myself included, up to a year ago) without understanding the implications.

There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

I don't see how that's relevant to this discussion. We're talking about non-deterministic updates, not non-deterministic functions. In any case, I'm not saying anything should be banned. I'm just expressing the opinion that this particular syntax should not be used unless there are compelling reasons to do so (and I don't deny that such compelling reasons do exist).

John
Post #1249037
Posted Wednesday, February 8, 2012 8:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:11 AM
Points: 1,851, Visits: 21,646
John Mitchell-245523 (2/8/2012)
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.

Fully documented - maybe, although a quick search just now didn't reveal anything. Well known - I'd dispute that. I think there are a lot of people who use it (myself included, up to a year ago) without understanding the implications.

There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

I don't see how that's relevant to this discussion. We're talking about non-deterministic updates, not non-deterministic functions. In any case, I'm not saying anything should be banned. I'm just expressing the opinion that this particular syntax should not be used unless there are compelling reasons to do so (and I don't deny that such compelling reasons do exist).

John


Documented here

http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx

In the section "Using UPDATE with the FROM Clause"



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537



Post #1249040
Posted Wednesday, February 8, 2012 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 6, 2015 8:41 AM
Points: 1,600, Visits: 1,167
John Mitchell-245523 (2/8/2012)
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?


Definitely not. I believe its important to be 100% aware of what value is about to be used. :)


There's no kill switch on awesome!
Post #1249048
Posted Wednesday, February 8, 2012 8:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Mark-101232 (2/8/2012)
Documented here

http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx

In the section "Using UPDATE with the FROM Clause"

John might also like to read the BOL entry below which documents some of the cases where a syntactically correct MERGE will produce incorrect results:

Optimizing MERGE Statement Performance

The Connect item for this sparked a lot of debate at the time. I'm with Erland, for the record. The upvote/down vote counts tell their own story too, of course.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1249051
Posted Wednesday, February 8, 2012 8:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 8, 2015 4:20 AM
Points: 1,932, Visits: 1,598
It’s a nice discussion but I see less practicality in it. Please look into the example that Joe has shared.

Do we really need to update Orders table based on Order Details table in any business case (except for the sum of orders)? Usually we update child table values from Parent table where there is precisely one value to update to child.

These scenarios we are discussing will most likely not appear in routine.


Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad
Post #1249108
Posted Wednesday, February 8, 2012 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:10 AM
Points: 5,820, Visits: 11,335
Documented here

http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx

In the section "Using UPDATE with the FROM Clause"

Thanks Mark - I knew it must be in there somewhere.

John might also like to read the BOL entry below which documents some of the cases where a syntactically correct MERGE will produce incorrect results:

Optimizing MERGE Statement Performance

Good catch, Paul. I wonder whether anyone has raised a Connect issue to have that corrected or the offending part of the syntax removed?

The Connect item for this sparked a lot of debate at the time. I'm with Erland, for the record. The upvote/down vote counts tell their own story too, of course.

Also for the record, I'm not really in favour of deprecation either. David Portas's was the most sensible comment I saw in that discussion.

John
Post #1249136
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse