6

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?

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • 1
    I'm curious about why you guys are curious about why Randy is curious. It's a good way of learning. – Lee Gunn Aug 14 '13 at 13:44
  • 2
    @LeeGunn - I've come to learn, in the 3 or 4 years I've been on SO that many people aren't interested in really answering questions. They are just interested in arguing. I've learned to ignore these folks. – Randy Minder Aug 14 '13 at 14:09

1 Answers1

11

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • +1 All SQLSkills blogs should be bookmarked by anyone working with SQL Server. – squillman Mar 18 '11 at 03:20
  • When I run your example, I get a SQL Server error on each of the %%physloc%% Select statements. Msg 102, Level 15, State 3, Line 2 Incorrect syntax near 'physloc'. – Randy Minder Mar 19 '11 at 22:52
  • @Randy *This can be easily tested on SQL Server **2008*** The linked article does state that this behaviour affects only changes to the clustering key and applies to 2005+ – RichardTheKiwi Mar 20 '11 at 07:30
  • 2
    +1 You can use `sys.fn_PhysLocFormatter(%%physloc%%)` for slightly more readable output (gives file:page:slot) – Martin Smith Apr 03 '11 at 11:24
  • The row moves when the column of the clustered index changes. But do you know in wich _other_ circumstances the %%physloc%% can change? – Olivier Faucheux May 02 '13 at 14:36
  • 1
    http://www.sqlskills.com/blogs/paul/do-changes-to-index-keys-really-do-in-place-updates/ seems a better link. – MattC Mar 05 '15 at 08:50