September 9, 2013 at 12:43 pm
dwain.c (9/9/2013)
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.
I was going to comment in favor of Hugo's Connect request, as I believe there should not be a non-standards-compliant method for doing something for which there is a standards-compliant method. However, before I could I had an interesting job which reminded me of one of the problems I have with MERGE:
'
Msg 5315, Level 16, State 1, Line 1
The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.
'
Now, you might think that I could have reworded the MERGE statement and run it on the other server, as the source of a MERGE statement can be a remote table, view, or a view over remote tables. But in this particular instance the remote server was a SQL2005 instance, and the source was a CLR table-value function, which for practical reasons could not be installed on the remote server, so I pretty much had to rely on some complex DELETE/UPDATE/INSERT statements.
The moral: don't remove the non-standard method until you've got the standard method at least on par with it.
September 9, 2013 at 3:06 pm
paul.knibbs (9/9/2013)
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!
The difference I see between UPDATE and MERGE statement is that you can more easily control your table source (hence provide uniqueness) than with update statement.
It would be bad if your update statement would break because of some extra data returned. Yes, you probably have incorrect data, but it's already data design problem.
September 9, 2013 at 3:13 pm
ChrisM@Work (9/9/2013)
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.
MERGE statement is NOT purely an UPDATE statement. It's INSERT, UPDATE and DELETE statements all at once. You cannot really say that it has to match with UPDATE statement behavior.
Let's say you are updating your records - the worse thing that can happen is that it's updated with a wrong value. Now you only need to go and redesign your logic and fix data. It's not that hard!
Now let's talk about MERGE. Depending on CASE condition, you can either delete, update or insert record. Imaging that for the same ID you return 2 different records, and one of them qualified for UPDATE, and other for DELETE. Which one takes priority? What should happen? There is no predictable outcome since the record can be just deleted. So, it's not about doing 2 update statements for the same record, it's more complex and applying those limitations was the right thing to do.
September 9, 2013 at 5:29 pm
Max-146500 (9/9/2013)
I was interested to verify the behaviour based on the PK ordering, and was relieved to see that the pk order is still used to establish the first matching row.
Just remember that is observed behaviour. There are no guarantees, and it really could change at any time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2013 at 6:21 pm
Roger L Reid (9/9/2013)
No reason for controversy except the inflammatory title (and if publicity is your goal, then even that's well done).Your point is that the generalist programmer often doesn't understand working in sets; and that (beyond the known issue of writing inelegant code) fact can cause them to get incorrect results. Is that about it? No controversy there AFAIC.
Roger - That is certainly part of it, but I think I meant to say there's more to it.
1. Possibly the "generalist" programmer has no idea of the described behavior in circumstances where it would apply.
2. I often see insufficient analysis done on the data structure/data itself to determine if and when a query has the potential to return duplicate rows.
3. Even when the analysis is done in point #2 (or simply the duplicates returned are observed without being investigated), the methods chosen to eliminate duplicates (e.g., DISTINCT) may not be the best fit depending on the business rules/performance considerations.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 9, 2013 at 6:24 pm
James_DBA (9/9/2013)
The first thing I thought of when looking at the code example is how the join was only part of the natural key used in table #test2. SET based programming immediately makes me think that both the ID and Row_No should be used, after all the point of that key in that table is to guarantee a unique row. Which if you think of including the Row_No field it immediately makes you eliminate the potential for duplicates (not to say this is the proper or only answer, as demonstrated by others earlier).
Yes indeed, you have suggested a perfectly valid way to eliminate the duplicate for this case.
Often times, the duplication is much more subtle than my (noted as) contrived example. I thought it best to stick to an example that was relatively easy for pretty much anyone to understand, rather than to try to come up with something much more esoteric that might obfuscate the behavior I was attempting to illustrate.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 9, 2013 at 9:03 pm
ChrisM@Work (9/9/2013)
Jeff Moden (9/9/2013)
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.
Oh yes - Hugo K's excellent talk in Nottingham earlier this year is still fresh in my mind, as is Dwain's article - and bugs too. My point is that they are inconsistent, not that MERGE is any better.
BWAA-HAAA!!! My point is that if you use them incorrectly, they will consistently give you "unexpected results". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2013 at 3:28 am
Hello
Very good and important article Thanks.
But while I was rate article, mouse didn't work or etc. and while trying to give 5 star, I think I gave 1 star 🙁
September 16, 2013 at 9:48 am
We write all our code in Oracle first, where PL/SQL is needed, then produce the tSQL version afterwards where that is different, so we'd never have this problem (and I do see it as a problem) of having inconsistency in what is going to be updated because the Oracle code wouldn't run.
September 16, 2013 at 6:27 pm
marlon.seton (9/16/2013)
We write all our code in Oracle first, where PL/SQL is needed, then produce the tSQL version afterwards where that is different, so we'd never have this problem (and I do see it as a problem) of having inconsistency in what is going to be updated because the Oracle code wouldn't run.
I've heard that Oracle prevents this inconsistency. What you propose for developing the code sounds a bit cumbersome, but heh if it works who am I to judge! At least in the end you have portable code.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 17, 2013 at 2:31 am
dwain.c (9/16/2013)
marlon.seton (9/16/2013)
We write all our code in Oracle first, where PL/SQL is needed, then produce the tSQL version afterwards where that is different, so we'd never have this problem (and I do see it as a problem) of having inconsistency in what is going to be updated because the Oracle code wouldn't run.I've heard that Oracle prevents this inconsistency. What you propose for developing the code sounds a bit cumbersome, but heh if it works who am I to judge! At least in the end you have portable code.
We use a 4GL that handles most of the SQL for us, we just have to write the odd bit ourselves - probably no more than 5% of the entire system, so it's not actually that cumbersome.
Edited for spelling (right/write, duh!)
September 17, 2013 at 11:04 pm
marlon.seton (9/17/2013)
dwain.c (9/16/2013)
marlon.seton (9/16/2013)
We write all our code in Oracle first, where PL/SQL is needed, then produce the tSQL version afterwards where that is different, so we'd never have this problem (and I do see it as a problem) of having inconsistency in what is going to be updated because the Oracle code wouldn't run.I've heard that Oracle prevents this inconsistency. What you propose for developing the code sounds a bit cumbersome, but heh if it works who am I to judge! At least in the end you have portable code.
We use a 4GL that handles most of the SQL for us, we just have to right the odd bit ourselves - probably no more than 5% of the entire system, so it's not actually that cumbersome.
Interesting. What 4GL do you use?
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 18, 2013 at 1:58 am
Proiv
We develop one code set for Windows, Sun & Linux with Oracle, then have to tweak it a bit to for Windows & SQLServer.
September 18, 2013 at 6:30 pm
marlon.seton (9/18/2013)
ProivWe develop one code set for Windows, Sun & Linux with Oracle, then have to tweak it a bit to for Windows & SQLServer.
Thanks! From your information it sounds like a pretty good tool.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 12:50 am
dwain.c (9/18/2013)
marlon.seton (9/18/2013)
ProivWe develop one code set for Windows, Sun & Linux with Oracle, then have to tweak it a bit to for Windows & SQLServer.
Thanks! From your information it sounds like a pretty good tool.
One can develop a system very quickly with it but you have to do some work to get good performance with relational databases.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply