Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Update records?
Add to briefcase
11 posts, Page 1 of 2
12»»

Update records? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
khushbu
Posted Tuesday, October 09, 2012 11:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:50 PM
Points: 267, Visits: 695
I have been hearing lately that SQL Server when performing update to any table, first deletes that set of records and then inserts the record with the new value.

Wonder how true is this? Havent come across any MSDN link. Can someone please shed some lilght on this.

Thanks
Post #1370686
Bhuvnesh
Posted Wednesday, October 10, 2012 2:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
khushbu (10/9/2012)
first deletes that set of records and then inserts the record with the new value.
i dont think it will work same as you explained for SAME value updations

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1370754
Jason-299789
Posted Wednesday, October 10, 2012 2:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836, Visits: 2,192
khushbu (10/9/2012)
I have been hearing lately that SQL Server when performing update to any table, first deletes that set of records and then inserts the record with the new value.

Wonder how true is this? Havent come across any MSDN link. Can someone please shed some lilght on this.

Thanks


Actually during an update SQL server (and as far as im aware all DB engines) do a Read, Delete, Insert.

I dont have access to an MSDN article to point you at, but if you could read the log files, and i think there are some tools that allow you to do that yuo would see this behaviour.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1370755
demonfox
Posted Wednesday, October 10, 2012 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 1,088, Visits: 1,118
khushbu (10/9/2012)
I have been hearing lately that SQL Server when performing update to any table, first deletes that set of records and then inserts the record with the new value.

Wonder how true is this? Havent come across any MSDN link. Can someone please shed some lilght on this.

Thanks

oh so it's all over the news ...
one way to confirm that is through output clause used for update statement.
for getting the updated record inserted.* is used , while record that got updated is retrievd through deleted.* ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1370765
khushbu
Posted Wednesday, October 10, 2012 2:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:50 PM
Points: 267, Visits: 695
I quickly made an example.. seems like its true..
However, was looking for any MSDN link..?

CREATE TABLE #Employee
(
EmpId int identity(1,1)
,EmpFirstName varchar(100)
,EmpLastName varchar(100)
,Empdept varchar(50)
)

INSERT INTO #Employee (EmpFirstName,EmpLastName,Empdept) values ('FirstName1','LastName12','Finance')
INSERT INTO #Employee values ('FirstName2','LastName2','COE-1')
INSERT INTO #Employee values ('FirstName3','LastName3','COE-2')
INSERT INTO #Employee values ('FirstName4','LastName4','COE-3')

select * from #Employee

UPDATE #Employee SET Empdept='Company COE' output Inserted.*,Deleted.* WHERE EmpId=4

select * from #Employee
Post #1370769
GilaMonster
Posted Wednesday, October 10, 2012 3:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 38,062, Visits: 30,355
It's partially true (but the inserted and deleted tables are not the way to prove it)

When the index key column is changed, then the update is split into delete/insert pair. If the update does not affect the index key column, then the update is performed as an in-place update.

The only way you can tell this is by looking in the transaction log and decoding the log records. The deleted and inserted tables always behave as you've seen, the deleted table contains the old values, the inserted the new. This is regardless of how the update is performed.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, 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 #1370779
Evil Kraig F
Posted Wednesday, October 10, 2012 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, June 13, 2013 6:44 PM
Points: 5,720, Visits: 6,192
I'll +1 Gail on this but most of my evidence is via her and a few others that I was privelidged to watch a discussion they had.

In general, you won't find much on the internals of the page updates, simply because MS doesn't want to end up with their arse in a sling because they changed something down the line because it worked better/fixed an issue. You can be pretty confident you'll never find anything official AND public past the syntactical stage of writing a query or examining the execution plan.

You've gone too deep in the code to really know (or usually need to care) without being an MVP and a direct line to the developers. *shrugs*

Out of curiousity, what drove this question? If you're trying to cure an issue and it led you to that question, I'd recommend you describe that and the folks who can help (and some who probably can't, like me) will attempt to assist you.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1370790
khushbu
Posted Wednesday, October 10, 2012 3:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:50 PM
Points: 267, Visits: 695
Evil Kraig F (10/10/2012)


Out of curiousity, what drove this question? If you're trying to cure an issue and it led you to that question, I'd recommend you describe that and the folks who can help (and some who probably can't, like me) will attempt to assist you.


Just during recent discussion and some reading I came across this notion and wandered if its true.. since never heard about it before.
Post #1370798
GilaMonster
Posted Wednesday, October 10, 2012 4:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 38,062, Visits: 30,355
Just a note, whether it's a split or an in-place update does not in any way affect the behaviour that you, as a user or developer will see. It won't change triggers or output clause or results. It's purely an internal behaviour.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, 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 #1370810
Gail Wanabee
Posted Friday, October 12, 2012 12:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 PM
Points: 254, Visits: 1,032
GilaMonster (10/10/2012)
....When the index key column is changed, then the update is split into delete/insert pair. If the update does not affect the index key column, then the update is performed as an in-place update.

The only way you can tell this is by looking in the transaction log and decoding the log records. The deleted and inserted tables always behave as you've seen, the deleted table contains the old values, the inserted the new. This is regardless of how the update is performed.


Gail,

In either case, is the sequence always "read, delete, write" or in the case of no modification to the index key column, is the sequence simply "read, write"?

For my own knowledge, I want to fully understand the cost of an UPDATE operation.
Post #1372315
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.