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


UPDATE when the values are the same


UPDATE when the values are the same

Author
Message
chris 76827
chris 76827
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 9
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?
Scott D. Jacobson
Scott D. Jacobson
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2204 Visits: 1020
I think what you mean is if you have a data set like this:

CREATE TABLE USERS ( TYPEID INT,USERNAME VARCHAR(30),)

INSERT VALUES (1,'JSMITH')
INSERT VALUES (2,'JDOE')
INSERT VALUES (1,'ASMITH')



and you do something like this:

UPDATE USERS
SET TYPEID = 3
WHERE TYPEID = 1


will the update statement update both JSMITH and ASMITH? The simple answer is yes, it will.
Sergiy
Sergiy
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39728 Visits: 12594
CELKO (8/12/2012)
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?


Who cares?


Triggers, for example. transaction logs. Users sitting in frount of the frozen UI waiting while the database finishes the useless activity.

Update dbo.Event
SET Status = 1
WHERE Eventdate < GETDATE()
and Status = 0

Without checking for the status value update will take forever on a big history table and add a big chunk to the Transaction Log file.
If there is a FOR UPDATE trigger it will be executed for all records having Eventdate in the past.

With Status = 0 added to the query it will actually update only recently added/processed records which have not been updated before. And trigger(s) will be run only for that smaller subset.

When I fixed similar query on one of production databases it removed 8GB of data added daily to the TRN file. Not to mention easing the pressure on the server, improving performance not only this but other systems having databases hosted on the same SQL Server.
chris 76827
chris 76827
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 9
Ask a simple yes/no question and get a few tangent replies, damn.

Its quite simple. You have a table as follows.


CREATE TABLE Test (
ID INT IDENTITY(1,1)
Name VARCHAR(100)
)

INSERT INTO Test (Name) VALUES ('Tom')
UPDATE Test SET Name = 'Tom' WHERE ID = 1



Does the update statement overwrite Tom even though Tom is already the value.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365723 Visits: 46933
Why are you asking, and at what level of the engine are you interested in? (because the behaviour is interesting and complex when you get down to the deep internals)

At a high level, yes it will. The row qualifies for the update, so the row gets updated, it will count towards the rows affected and the row will appear in the inserted and deleted pseudo-tables in triggers (and in the OUTPUT clause)

When it comes to logging and to the actual modifications of the pages, it's more complex. It's not a simple yes/no question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chris 76827
chris 76827
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 9
Okay so getting a little more specific. Does the SQL Server edit the MDF file and overwrite the current value of that record or does it carry out a check to see if their the same to save writing to the HDD something that's already there.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365723 Visits: 46933
Well... To start all changes are made in memory and not to disk, and the changes to the files on disk are done later (by a background process) at a page level, not a row level...

As far as I can tell, updating a row and not actually changing the values does not mark the page as dirty, meaning it won't be written back to disk when the checkpoint process runs.
I'm not saying it isn't ever marked dirty, but in my simple tests with a single row in a table, updating the name to itself did not appear to mark the page as dirty.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chris 76827
chris 76827
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 9
Yeah sorry I appreciate its all done in memory but I was referring to the write back stage.

That's pretty much answered my question, cheers.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85048 Visits: 21669
chris 76827 (8/13/2012)
Yeah sorry I appreciate its all done in memory but I was referring to the write back stage.

That's pretty much answered my question, cheers.


That does not mean that you should not do the check though:

update x
set y = 3
where y <> 3 or y is null



will run faster, in my experience, than

update x
set y = 3



especially if many of the rows are already 3.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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