All,
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:
>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
