Back To Basics: Whats an update
-
sqlzealot-81 (6/6/2011)
Hi Muthu,Can you provide us a link please?I don't have link right now. If i got i'll post you.
Sometimes back i've read this kalen's book.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
Gail, Got the link
UPDATE Statements May be Replicated as DELETE/INSERT Pairs
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
muthukkumaran Kaliyamoorthy (6/6/2011)
Gail, Got the linkWhat that says is IF the update is split into a delete/insert combo THEN the replication sends a delete and an insert operation.
If any column that is part of a unique constraint is updated, then SQL Server implements the update as a "deferred update", which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers.
It does not say that if there's replication all updates are implemented as deferred 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is what I determined by browsing the logs.
Additionally, if you use a horizontal filter in your publication and if the updated row does not meet a filter condition, only a DELETE procedure call is sent to the subscribers. If the updated row previously did not meet the filter condition but meets the condition after the update, only the INSERT procedure call is sent through the replication process.
You're quite right. I only saw it apply to horizontal filters.
-
LutzM (6/6/2011)
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.LutzM,
Here is the test
Create table test (n int,name char(10))
Create table test1 (n int,name char(10),n1 int,name1 char(10))
insert into test values (1,'muthu')
insert into test values (2,'lutzm')
insert into test values (3,'gail')
insert into test values (4,'ssc')
insert into test values (5,'ssc')
create trigger tr_update on test
after update
as
begin
insert into test1 select * from inserted as new,deleted old
end
update test set n=10 where name='muthu'
select * from test1
update test set n=122 where name='ssc'
select * from test1
update test set n=20 where name='muthu'
select * from test1
Edited : to correct the syntax
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
Ok, let me try and clarify things.
In many cases SQL will implement an update as exactly that. An in-place update where it just changes the values in the row. This happens for both for heaps and for tables with a clustered index (as long as the key columns are not changing) and it happens regardless of the number of rows affected (lots of rows won't result in an update being split)
If the key columns of an index are changed, SQL always splits that update into a delete and insert pair of operations. http://sqlskills.com/BLOGS/PAUL/post/Do-changes-to-index-keys-really-do-in-place-updates.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
muthukkumaran Kaliyamoorthy (6/6/2011)
Here is the testCreate table test (n int,name char(10))
Create table test1 (n int,name char(10),n1 int,name1 char(10))
insert into test values (1,'muthu')
insert into test values (2,'lutzm')
insert into test values (3,'gail')
insert into test values (4,'ssc')
insert into test values (5,'ssc')
create trigger tr_update on test
after update
as
begin
insert into test1 select * from inserted as new,deleted old
end
update test set n=10 where name='muthu'
select * from test1
update test set n=122 where name='ssc'
select * from test1
update test set n=20 where name='muthu'
select * from test1
Edited : to correct the syntax
How does that prove that SQL splits the update into a delete/insert pair? (hint, it doesn't)
With an update trigger, the old values always go into the deleted table and the new values always go into the inserted table (there's no updated table). This has no reflection at all on how SQL handles the operation internally. To tell that, one would have to read the transaction log.
The transaction log, for the first update of your test, shows this:
The operation for the update was a LOP_Modify_row. An in-place update. If that had been a split update, the log would show a delete operation and an insert operation. It does not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail
This means if i have primary key (C.I) then the SQL will do the delete followed by an insert to the table and PK.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
-
muthukkumaran Kaliyamoorthy (6/6/2011)
GailThis means if i have primary key (C.I) then the SQL will do the delete followed by an insert to the table and PK.
Only if your update changes the key values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
GailThis means if i have primary key (C.I) then the SQL will do the delete followed by an insert to the table and PK.
Only if your update changes the key values.
thanks gail,
I did the test from paul's link
Create table test (n int primary key,name char(10))insert into test values (1,'muthu')
insert into test values (2,'lutzm')
insert into test values (3,'gail')
insert into test values (4,'ssc')
insert into test values (5,'ssc')
create trigger tr_update on test
after update
as
begin
select * from inserted as new,deleted old
end
update test set n=10 where name='muthu'
update test set n=122 where name='ssc'
i have just added the PK to the test table and checked the t-log and an clustered index(PK) has been changed.
SELECT [Current LSN], [Operation], [Context],
[Log Record Length], [Page ID], [Slot ID] FROM fn_dblog (NULL, NULL);
new row is in slot 1 (the LOP_INSERT_ROWS record) and slot 0 is ghosted (the LOP_DELETE_ROWS record).
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
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.
Only if your update changes the key values.
So this also true, Because the replication nees a primary key to setup.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
muthukkumaran Kaliyamoorthy (6/6/2011)
Only if your update changes the key values.
So this also true, Because the replication nees a primary key to setup.
Yes, l replication needs a key, but it doesn't require you to change the key value on every update. If you only change non-key values, SQL can still do 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/6/2011)
muthukkumaran Kaliyamoorthy (6/6/2011)
Only if your update changes the key values.
So this also true, Because the replication nees a primary key to setup.
Yes, l replication needs a key, but it doesn't require you to change the key value on every update. If you only change non-key values, SQL can still do an in-place update.
Thanks gail.
Leaned a bit internal today.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
Leaned a bit internal today.
Yes me too. Thanks Gail!!!
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply