Back To Basics: Whats an update

Question

Back To Basics: Whats an update

Latheesh NK

SSCertifiable

Points: 6559
More actions
June 6, 2011 at 12:47 am

#238709

Hi, Thought of going back to basics, Whats an update. Is it an operation of delete and insert?

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

sqlzealot-81 (6/6/2011)
Hi, Thought of going back to basics, Whats an update. Is it an operation of delete and insert?

Yep, True the update will delete the value first and insert it.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

LutzM SSC Guru Points: 107049 More actions · Answer 2

The easiest way to verify it would be to add a AFTER UPDATE trigger to a test table and insert the results of the internal DELETED and INSERTED tables into an audit table. Then insert a row and perform an update.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

muthukkumaran Kaliyamoorthy (6/6/2011)
sqlzealot-81 (6/6/2011)
Hi, Thought of going back to basics, Whats an update. Is it an operation of delete and insert?
Yep, True the update will delete the value first and insert it.

Not in most cases it won't.

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 4

I tested sometimes back where I found update deletes and then inserts.

However, when I think about the replication, say, I am having a transactional replication, If I update some data in publisher DB,(say, it deletes and inserts), then the same transaction must be applied to subscriber in the same sequense right? But I can see the delete and inserts are very less in this case.

Any thoughts?

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 5

GilaMonster (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
sqlzealot-81 (6/6/2011)
Hi, Thought of going back to basics, Whats an update. Is it an operation of delete and insert?
Yep, True the update will delete the value first and insert it.
Not in most cases it won't.

To clear myself, What happens in most cases?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

An in-place update.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

GilaMonster (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
sqlzealot-81 (6/6/2011)
Hi, Thought of going back to basics, Whats an update. Is it an operation of delete and insert?
Yep, True the update will delete the value first and insert it.
Not in most cases it won't.

Yes I second that.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

GilaMonster (6/6/2011)
An in-place update.

@sqlzealot-81,

What gail told is

An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 9

muthukkumaran Kaliyamoorthy (6/6/2011)
GilaMonster (6/6/2011)
An in-place update.
@sqlzealot-81,
What gail told is
An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.

How do we differ the transaction whether its in-place or multi-phase operation?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

sqlzealot-81 (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
GilaMonster (6/6/2011)
An in-place update.
@sqlzealot-81,
What gail told is
An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.
How do we differ the transaction whether its in-place or multi-phase operation?

You don't change anything. SQL does under the covers, it's not something you need to worry about.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

muthukkumaran Kaliyamoorthy (6/6/2011)
GilaMonster (6/6/2011)
An in-place update.
@sqlzealot-81,
What gail told is
An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.

So are you saying that if there's replication or an update trigger, SQL will always split an update into a delete/insert? Prove it or cite it please.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 12

sqlzealot-81 (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
GilaMonster (6/6/2011)
An in-place update.
@sqlzealot-81,
What gail told is
An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.
How do we differ the transaction whether its in-place or multi-phase operation?

SQL server will manage this.Its huge part.

It depends upon the number of rows affect by an update st and whether the index key has to changed.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 13

Latheesh NK

SSCertifiable

Points: 6559

June 6, 2011 at 2:45 am

#1334241

Hi Muthu,Can you provide us a link please?

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 14

GilaMonster (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
GilaMonster (6/6/2011)
An in-place update.
@sqlzealot-81,
What gail told is
An inplace update only change the byte.But an exception update trigger and replication.This as you told delete followed by an insert.
So are you saying that if there's replication or an update trigger, SQL will always split an update into a delete/insert? Prove it or cite it please.

I dont have a quote to show you but have spent many hours seeing it when reviewing transaction logs in ApexSql log. I determined it was required when using a filtered publication so rows can be deleted and inserted depending on meeting the filter criteria.

I should say, this is how it is logged and not necessarily the action performed on the publisher database. I didnt track this.