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

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

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

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

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.

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

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