Update statement question
-
I'm doing a DB review and have always been taught to write updates with a Join like using this syntax.
Update Target
Set ColList=s.ColList
From
Table1 Target
JOIN Table2 Source on Target.NaturalKey=Source.NaturalKey
where
Target.ColList!=s.ColList
but I've noticed the following syntax being used in several cases
Update Target
Set ColList=s.ColList
From
Table2 Source
JOIN Table1 Target on Target.NaturalKey=Source.NaturalKey
where
Target.ColList!=s.ColList
Are there any difference or performance implications in using the second syntax? or are they effectively both the same?
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Both statements will perform exactly the same update, with most likely exactly the the same performance.
However, it's considered to be a good practice to drive your query from the smallest set (table).
Saying above, I have only seen real performance implication in a complex multi-JOIN queries.
-
Thanks Eugene, it just looks odd to my eyes.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (11/21/2012)
Thanks Eugene, it just looks odd to my eyes.you can test it with actual execution plan . that will give you exact picture what/where/why any object is making difference (if any 😀 😉 )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh (11/21/2012)
Jason-299789 (11/21/2012)
Thanks Eugene, it just looks odd to my eyes.you can test it with actual execution plan . that will give you exact picture what/where/why any object is making difference (if any 😀 😉 )
Its not exactly practical, as the DB is a 2 TB DW, and requires a restore after each run as its based in an offsite server in order to tune the ETL process.
It just struck me as odd that the Target table wasnt in the FROM and the Source in the join, basically it looked wierd, as I've always coded updates as per my first query.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
CELKO (11/22/2012)
I'm doing a DB review and have always been taught to write updates with a JOIN like using this syntax.
UPDATE Target
SET col_list = S.col_list
FROM Table1 AS Target
INNER JOIN
Table2 AS Source
ON Target.NaturalKey=Source.NaturalKey
WHERE Target.col_list <> S.col_list;
but I've noticed the following syntax being used in several cases ..
You were taught a 1970's Sybase dialect and need to unlearn it. You even used the old != that we had from the Sybase/UNIX days. It has an interesting history of cardinality problems. They both suck and make you look like a hillbilly. :w00t:
Today, we have the ANSI/ISO Standard MERGE statement which has no cardinality problems and will port. Read about it. And do a bulk change of those old !=, ISNULL and other 1970's code before the other kids see your old code and make fun of you in gym class. 🙂
Here we go again. Dear J.C. you do need to calm down.
"!=" has nothing to do with 1970's Sybase and/or UNIX.
Yes it was used old times, but it's still in use in modern technologies (eg. C# .NET). And I also, can tell you (keep it secret please) that will port easily into most of existing RDBM's (eg. ORACLE)
If your UPDATE with JOIN is written correctly, you very unlikely to have cardinality issues with it.
Actually, MERGE is quite new thing in T-SQL and it is definitely useful. But, right now, I think it is less "portable" than "!=", for example MySql has different way to do upserts...
-
No offence joe, my reason for asking was due to slivers of memories about there being a possible performance hit with the update table being in the Join rather than the from and just wanted to clarify if this was still the case.
In regards to the != Its an old colding habit from a previous life of being a c/c++ programmer. :Whistling:
I do know about the merge, and it has its place, but there are occasions when it doesnt do as well as an update, horses for courses.
In regards to your other comments, meh, people are entitled to an opinion, if its worth considering I'll consider it, if not it gets thrown away with all the other junk.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
CELKO (11/22/2012)
I'm doing a DB review and have always been taught to write updates with a JOIN like using this syntax.
UPDATE Target
SET col_list = S.col_list
FROM Table1 AS Target
INNER JOIN
Table2 AS Source
ON Target.NaturalKey=Source.NaturalKey
WHERE Target.col_list <> S.col_list;
but I've noticed the following syntax being used in several cases ..
You were taught a 1970's Sybase dialect and need to unlearn it. You even used the old != that we had from the Sybase/UNIX days. It has an interesting history of cardinality problems. They both suck and make you look like a hillbilly. :w00t:
Today, we have the ANSI/ISO Standard MERGE statement which has no cardinality problems and will port. Read about it. And do a bulk change of those old !=, ISNULL and other 1970's code before the other kids see your old code and make fun of you in gym class. 🙂
No, we don't need to unlearn anything. Like anything else, UPDATE works just fine if you use it correctly. MERGE has its own set of problems, as well. And true portability is a myth, so don't worry about that either.
You also shouldn't quibble too much about 1970's code when you still use things like a push-stack While loop to do nested sets conversions. It "make
you look like a hillbilly [sic]".--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
...MERGE has its own set of problems...
any major problems ? If so, is it not fixed in the latest version by Microsoft SQL Team?
karthik
-
what other alternative we can take here instead of "!= "
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh (11/22/2012)
what other alternative we can take here instead of "!= "You can use "<>" which is another notation for not equal, which is what Joe is suggestion everyone should use.
If memory serves me it comes from the BASIC language group (citation!!), where as Java, C#, Python (?) all use != to do NOT EQUAL operations.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (11/23/2012)
Bhuvnesh (11/22/2012)
what other alternative we can take here instead of "!= "You can use "<>" which is another notation for not equal, which is what Joe is suggestion everyone should use.
If memory serves me it comes from the BASIC language group (citation!!), where as Java, C#, Python (?) all use != to do NOT EQUAL operations.
And T-SQL proudly supports both syntaxes.
So, it's a personal choice. Usually developers with C,C++,C# and Java background use "!=", VB and others "<>".
If someone has a desire to port SQL Server into other RDBMS, I can assure you, using "!=", "ISNULL" and other MS SQL proprietary function/syntax is going to be your least problem, same as would be porting Oracle to SQL...
Joe is stack in his earlier days and very often refers to technologies unknown and unseen by young developers (actually, when I want to amuse some young techies, I bring punch card to office - it causes a small furor, many people doesn't even know what it is and who knows, only seen it in a form of picture).
Actually, I do fail to understand logic behind Joe referral to something as been used in 1970. There are a lot of thing was used then and still in use now as it's perfectly serves the needs. Just look around, all things do not change every year. Yes we do have few new staff to play with, but we are still using spoons and forks...
-
I agree Eugene, I dont remember punch cards but I do remember 8 inch floppy disks, if you had a 386 as a desktop you were lucky, and servers ran on 486 architectures and when 100Gb disk storage was considered excessive.
I must say i do feel as though I've made it here on SSC by being flamed by JC. 😛
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
CELKO (11/23/2012)
...Why use needless dialect that is about to be deprecated?
About to be deprecated? Have you got a link for that?
If your talking about this connect issue, it's already been closed as 'won't fix'
____________________________________________________
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
-
CELKO (11/23/2012)
No, we don't need to unlearn anything. Like anything else, UPDATE works just fine if you use it correctly.
That is an awful argument and you know it! Why would anyone deliberately build fragile, proprietary system that return non-deterministic results? Why use needless dialect that is about to be deprecated?
It's not about to nor is it ever likely that the proprietary version of UPDATE will ever be deprecated because too many people have written code that uses it.
Why would anyone build a SELECT that uses non-sargable criteria? The answer is simply because they didn't know how to use the criteria in the FROM clause correctly. The same holds true with UPDATE. If you don't know how to use it correctly, then it'll do weird things to you just like any other code.
I don't care if it's proprietary because true portability is nothing but a myth.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply