SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Hazard of Using the SQL Update Statement


A Hazard of Using the SQL Update Statement

Author
Message
dwain.c
dwain.c
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36657 Visits: 6431
Comments posted to this topic are about the item A Hazard of Using the SQL Update Statement


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)

Group: General Forum Members
Points: 433355 Visits: 43494
This is a great article in that it makes people, especially newbies who may have not studied the nuances of the T-SQL UPDATE statement to its fullest depth, aware of what most people would consider to be a fault.

I, however, do not consider the fact that it won’t give you an error for an attempted multiple-update on a single row a fault. Nay. I consider it to be a feature! I actually hate Oracle for trying to be so dutiful as to thwart my attempts to get something done by producing errors for things that I actually want to do.

I successfully used this UPDATE “feature” to build CSV columns in a high performance, set-based manner in SQL Server 7 before the advent of UDFs or FOR XML PATH to do the same. I’m not sure what I’d use the “feature” for now but, if it should ever come up, I’d get pretty well bent out of shape if I got an error because some product language programmer decided that it wasn’t ever supposed to be done.

To wit, if you make something idiot proof, only idiots will use it. ;-)

As you say, "Let the controversy begin." :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
natalie.ignatieva
natalie.ignatieva
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 438
Jeff Moden (9/8/2013)


I, however, do not consider the fact that it won’t give you an error for an attempted multiple-update on a single row a fault. Nay. I consider it to be a feature! I actually hate Oracle for trying to be so dutiful as to thwart my attempts to get something done by producing errors for things that I actually want to do.

I totally agree with you about that it's a feature and it's not a hazard.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82906 Visits: 20485
natalie.ignatieva (9/9/2013)
Jeff Moden (9/8/2013)


I, however, do not consider the fact that it won’t give you an error for an attempted multiple-update on a single row a fault. Nay. I consider it to be a feature! I actually hate Oracle for trying to be so dutiful as to thwart my attempts to get something done by producing errors for things that I actually want to do.

I totally agree with you about that it's a feature and it's not a hazard.


Prior to the introduction of MERGE I'd have agreed with this - we learned to ensure that multiple source rows for a target row had the same source value. Now though? It's inconsistent - and changing the behaviour of UPDATE to match the strictness of MERGE would break too much legacy code.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
dwain.c
dwain.c
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36657 Visits: 6431
Jeff Moden (9/8/2013)
This is a great article in that it makes people, especially newbies who may have not studied the nuances of the T-SQL UPDATE statement to its fullest depth, aware of what most people would consider to be a fault.

I, however, do not consider the fact that it won’t give you an error for an attempted multiple-update on a single row a fault. Nay. I consider it to be a feature! I actually hate Oracle for trying to be so dutiful as to thwart my attempts to get something done by producing errors for things that I actually want to do.

I successfully used this UPDATE “feature” to build CSV columns in a high performance, set-based manner in SQL Server 7 before the advent of UDFs or FOR XML PATH to do the same. I’m not sure what I’d use the “feature” for now but, if it should ever come up, I’d get pretty well bent out of shape if I got an error because some product language programmer decided that it wasn’t ever supposed to be done.

To wit, if you make something idiot proof, only idiots will use it. ;-)

As you say, "Let the controversy begin." :-D


Actually I wasn't really suggesting that this is a bug (or a feature) that should be removed. Maybe a more clear statement of the hazard is in order:

- It is hazardous to allow UPDATE to perform its action on multiple rows where the results could be ambiguous, because even though it may allow the UPDATE to run, it might not give you the results that you expect.

No so with MERGE, which is pretty clear and doesn't allow it. Should it? I'll leave that for the intrepid MS SQL designers to tell us. But I think that newbie developers could get trapped by its (UPDATE's) behaviour, not understanding how it worked.

I am happy to be a bit controversial, even though in this case I'm not sure I really would take sides, despite what I said about the MERGE statement error message. On the surface, it would seem less ambiguity would be better.

I would be curious exactly to hear what you did to achieve your high-performance CSV columns (without being judgmental of course regarding data normalization) and whether there's any benefit to having it around now. Aside from the legacy code of course.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7062 Visits: 6240
natalie.ignatieva (9/9/2013)
I totally agree with you about that it's a feature and it's not a hazard.


I would say it's entirely possible for it to be both. Yes, it's a feature, and quite possibly a useful feature in some circumstances, but it's also a potential hazard waiting to catch the unwary. I fortunately generally work with data where it's quite easy to guarantee the uniqueness of each row, but not everyone has that luxury!
Paul White
Paul White
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68868 Visits: 11399
dwain.c - Article body
This is interesting. We updated the Value column of our #Test1 table twice for each row. Or did we? [...(later)...] In reality we find that somehow the SQL Server Query Optimizer was smart enough to decide that it only needed to update each row in our #Test1 table once. How it chose the row to use to make that update will ultimately remain its own dirty little secret.

There's a Stream Aggregate in the query plan. It applies an ANY() aggregate to the Value column, grouped by ID. Therefore, Only one update per row is applied at the Clustered Index Update iterator. How the ANY() aggregate chooses a Value per ID group is undocumented, and therefore undefined.

dwain.c - Article body
Since we always hear that SQL does not guarantee row ordering without an explicit ORDER BY clause, we assume that the same case is true here.

Indeed. ORDER BY only guarantees the final (presentation) order anyway, it says nothing about row order between query plan iterators, and nothing should be inferred.

dwain.c - Article body
In this particularly contrived example, there’s no way to ensure the distinctness of the rows you’re applying as the source to the target table for the update [...] Why couldn’t our UPDATE statement throw a nice clear warning like that?

Well, you could write the query using SQL standard syntax:

UPDATE #Test1
SET Value =
(
SELECT b.Value
FROM #Test2 AS b
WHERE b.ID = #Test1.ID
)


The query plans contains the same Stream Aggregate, but also an Assert to check the subquery returns one row per iteration. An error is thrown when multiple Values are encountered.

dwain.c - Article body
Is it a good thing to have a piece of code like this running in Production, where the results of the update may be unpredictable?

Probably not, unless non-deterministic results are intended. A non-deterministic update is almost always not the programmer's intention in my experience.

A 2008 Connect suggestion by Hugo Kornelis to deprecate the UPDATE FROM and DELETE FROM T-SQL extension syntax generated some good discussion, but overall it is one of the most down-voted I have encountered:

http://connect.microsoft.com/SQLServer/feedback/details/332437



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
dwain.c
dwain.c
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36657 Visits: 6431
Excellent feedback Paul! Absolutely nothing in it I could argue with.

Didn't know about the SQL Connect article (quite interesting) but I did know the syntax wasn't ANSI standard, even though I tend to use it a lot. Sort of glad it got voted down.

I will say I am honored that you took the time to look in on this and keep me honest.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
tomd-982312
tomd-982312
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 54
Good article and not that good example.

----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)SSC Guru (433K reputation)

Group: General Forum Members
Points: 433355 Visits: 43494
ChrisM@Work (9/9/2013)
Prior to the introduction of MERGE I'd have agreed with this - we learned to ensure that multiple source rows for a target row had the same source value. Now though? It's inconsistent - and changing the behaviour of UPDATE to match the strictness of MERGE would break too much legacy code.


Are you aware of the problems that they've had with MERGE? Some are "problems" with the way people implement it, just like the "problems" with UPDATE.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search