Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

UPDATE when the values are the same Expand / Collapse
Author
Message
Posted Sunday, August 12, 2012 4:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?
Post #1343937
Posted Sunday, August 12, 2012 8:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 30, 2015 6:31 PM
Points: 358, Visits: 957
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.
Post #1343952
Posted Sunday, August 12, 2012 10:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 27, 2015 7:05 AM
Points: 4,645, Visits: 8,527
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.
Post #1343969
Posted Monday, August 13, 2012 1:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, 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.
Post #1344001
Posted Monday, August 13, 2012 2:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 42,512, Visits: 39,331
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
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

Post #1344004
Posted Monday, August 13, 2012 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, 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.
Post #1344046
Posted Monday, August 13, 2012 3:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 42,512, Visits: 39,331
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
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

Post #1344050
Posted Monday, August 13, 2012 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, 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.
Post #1344052
Posted Monday, August 13, 2012 4:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 5,978, Visits: 14,426
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

I had some words with my wife, and she had some paragraphs with me.
Post #1344065
Posted Monday, August 13, 2012 10:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 27, 2015 7:05 AM
Points: 4,645, Visits: 8,527
GilaMonster (8/13/2012)
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.

Actually, it does.

I can offer you a simple test.
Create a new database with FULL recovery mode. Create a single table in it.
Insert several rows of data.
Then run a script having a loop with a single statement:

UPDATE MyTable
Set ColValue = ColValue

And watch the Log file growing.
What means that pages are certainly written to disk.
Post #1344520
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse