October 9, 2012 at 11:38 pm
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
October 10, 2012 at 2:13 am
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:-D
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 10, 2012 at 2:20 am
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
October 10, 2012 at 2:34 am
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 :ermm:
October 10, 2012 at 2:47 am
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
October 10, 2012 at 3:11 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2012 at 3:32 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 10, 2012 at 3:40 am
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.
October 10, 2012 at 4:05 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2012 at 12:14 pm
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.
October 12, 2012 at 12:21 pm
Lee Crain (10/12/2012)
For my own knowledge, I want to fully understand the cost of an UPDATE operation.
I would suggest you build a test table and experiment and see for yourself. Best way to learn.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply