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

Are all updates split into delete-insert?

This should be another quick one.

Earlier I saw a forum post where someone asserted that SQL always executes an update as a delete of the row and then an insert of the row with the new values. Now I need another excuse to play with undocumented features, so let’s see if that’s true (or at least true for all the common cases I try)

First up, a heap, no indexes at all, an update that does not change the size of the row.

CREATE TABLE TestingUpdate1 (
ID INT IDENTITY,
SomeString CHAR(50)
)

INSERT INTO TestingUpdate1 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate1
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate1

UpdateHeapFixedSize

The log operation here is Modify Row. so in this case, the update was done as an in-place update.

Second test, a heap, no indexes at all, and an update that changes the row size (however plenty free space on the page, so no issues with forwarding pointers here)

CREATE TABLE TestingUpdate2 (
ID INT IDENTITY,
SomeString VARCHAR(50)
)

INSERT INTO TestingUpdate2 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate2
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate2

UpdateHeapChangingSize

Again we have a modify row, so that again was an in-place update.

Last on the heap, let’s see if things change when there’s lots and lots of rows (I’m going to use an update that does not change the row size, I don’t want to involve forwarding pointers in the discussion here)

CREATE TABLE TestingUpdate3 (
ID INT IDENTITY,
SomeString CHAR(50)
)

INSERT INTO TestingUpdate3 (SomeString)
SELECT TOP (1000000) ' ' FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate3
SET SomeString = 'Something'

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate3

UpdateHeapLotsOfRows

Still the modify row operation, so it’s not a case that lots of rows will cause SQL to split the update into a delete-insert pair.

Moving on, let’s try a table with a clustered index, an update of a non-key column that does not change the size of the row. Anyone willing to bet what we’ll see?

CREATE TABLE TestingUpdate4 (
ID INT IDENTITY,
SomeString CHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate4 (ID)

INSERT INTO TestingUpdate4 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate4
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate4

The log records are a little more complex, there’s a few more than in the previous case, but the operation for the update is still the same – LOP_Modify_Row

UpdateClusterFixedSize

Second test with a clustered index, an update of a non-key column that does change the size of the row.

CREATE TABLE TestingUpdate5 (
ID INT IDENTITY,
SomeString VARCHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate5 (ID)

INSERT INTO TestingUpdate5 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate5
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate5

Still have a modify row operation here. Still an in-place update.

UpdateClusterChangingSize

Lastly an update of the clustered index key value.

CREATE TABLE TestingUpdate6 (
ID INT,
SomeString CHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate6 (ID)

INSERT INTO TestingUpdate6 (ID, SomeString)
VALUES
(1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate6
SET SomeString = 'NotFour',
ID = 42
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate6

SplitUpdate

Now we do have a split update. We’ve got a delete_rows and an insert_rows operation in the log. This was not done as an in-place update

So what can we conclude here? Does SQL do all updates as split updates?

It should be clear that, for cases where the index key is not changed, SQL can do updates as in-place updates. I’m not going to try and claim that it always will, that would be silly, there are lots of scenarios that I haven’t looked at (page splits and forwarded rows being among the most obvious), but it can and will do in-place updates.

For updates that change the key values, SQL will not do those as in-place updates. Paul explained that in one of his debunking posts a while back – http://sqlskills.com/BLOGS/PAUL/post/Do-changes-to-index-keys-really-do-in-place-updates.aspx

Comments

Posted by Steve Jones on 22 June 2011

Excellent post, and nice demonstration of how things can work. I was thinking the clustered key might be a delete/insert.

Posted by ThomasLL on 22 June 2011

Another good one, thanks Gail.

Thomas

Posted by Nakul Vachhrajani on 23 June 2011

Super! Thanks, Gail! This is definitely a myth-buster.

Posted by aquilahanise on 24 June 2011

I have learnt something new today... Great Post GilaMonster!!!

LOL @ Nakul Vachhrajani!

Posted by jmanly on 24 June 2011

Fantastic analysis, thanks v much for that.

Posted by mpv-1060508 on 24 June 2011

Is the in-place update still seen by a trigger as having a row in the deleted and inserted tables?

Posted by pmcpherson on 24 June 2011

First, thanks!  This was an eye opener for me.

Second, it does leave me a bit confused concerning triggers and I would appreciate help understanding why there is a Deleted table in a trigger.  Is this just a poorly named table?  Is it left over from previous versions of SQL?  It did leave me the impression that every update was a delete/insert combo.

Posted by tfifield on 24 June 2011

Great article Gail.  I was kind of thinking that an update to a cluster key would have to do a delete and insert in order to move it, but didn't have any proof.

Todd Fifield

Posted by GilaMonster on 24 June 2011

Re triggers, there is no updated table. There's an inserted which contains new rows in an insert and new values in an update and a deleted table that contains the removed rows in a delete and the old values in an update. That's how triggers work and the internal operations do not affect that.

It's a requirement of relational databases that the internal storage and internal operations do not affect the way the DB behaves. Hence no matter how the update is processed, the triggers will see the same things and behave the same way.

Posted by NachiM on 12 July 2012

Very useful post.

Thanks,

Nachi

Posted by Martin Smith-178018 on 29 December 2012

One other case you might have looked at is updates of a key column in a unique index stackoverflow.com/.../73226

Leave a Comment

Please register or log in to leave a comment.