SQL Server - What happens when a row in a table is updated?

Question

I seem to recall that when a row in a table is updated, SQL Server first deletes the row, and then re-adds the row, with the same Identity value for a column, if such a column exists. Can anyone confirm this?

Answer 1

False. The data is changed in place, within the same page under most circumstances. With SQL Server 2008, you can actually interrogate where the data resides on the disk, which will reveal as much.

Having actually looked at it now, I take it all back:

http://www.sqlskills.com/BLOGS/PAUL/category/On-Disk-Structures.aspx

This can be easily tested on SQL Server 2008. (code modified from linked article)

CREATE TABLE test (c1 INT, c2 VARCHAR (2000));
GO
CREATE CLUSTERED INDEX test_cl ON test (c1);
GO
CHECKPOINT;
GO
INSERT INTO test VALUES (1, REPLICATE ('Paul', 500));
GO
CHECKPOINT;
select %%physloc%%, * from test    -- 0x3E01000001000000
GO
UPDATE test SET c1 = 2 WHERE c1 =1;
GO
select %%physloc%%, * from test    -- 0x3E01000001000100
                                                     ^
                                                     |
                                    notice it has changed location