{"id":602,"date":"2011-02-25T09:22:00","date_gmt":"2011-02-25T09:22:00","guid":{"rendered":"\/blogs\/paul\/post\/Do-changes-to-index-keys-really-do-in-place-updates.aspx"},"modified":"2017-04-13T09:53:37","modified_gmt":"2017-04-13T16:53:37","slug":"do-changes-to-index-keys-really-do-in-place-updates","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/","title":{"rendered":"Do changes to index keys really do in-place updates?"},"content":{"rendered":"

\nThere was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (<\/font>blog<\/font><\/a>|<\/font>twitter<\/font><\/a>)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit :-) describe the process, but they describe it incorrectly unfortunately. They say that the record will remain in the exact same location on the same page and only the key storage bytes need to change. <\/font>\n<\/p>\n

\nThis is not true. True in-place updates of index key values do not happen in 2005 onwards. Let's go down the rabbit hole…<\/font>\n<\/p>\n

\nHere's a simple example: <\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE KeyUpdateTest;
\n\tGO
\n\tUSE KeyUpdateTest;
\n\tGO
\n\tALTER DATABASE KeyUpdateTest SET RECOVERY SIMPLE;
\n\tALTER DATABASE KeyUpdateTest SET AUTO_CREATE_STATISTICS OFF;
\n\tALTER DATABASE KeyUpdateTest SET AUTO_UPDATE_STATISTICS OFF;
\n\tGO
\n\tCREATE TABLE test (c1 INT, c2 VARCHAR (2000));
\n\tGO
\n\tCREATE CLUSTERED INDEX test_cl ON test (c1);
\n\tGO
\n\tCHECKPOINT;
\n\tGO
\n\tINSERT INTO test VALUES (1, REPLICATE ('Paul', 500));
\n\tGO
\n\tCHECKPOINT;
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow we look at the page itself (and you can get the stored-proc I use below from my old blog post <\/font>Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work<\/font><\/a>): <\/font>\n<\/p>\n

\n

\n\tEXEC sp_allocationMetadata 'test';
\n\tGO <\/font>\n\t<\/p>\n

\n\tObject Name Index ID Alloc Unit ID     Alloc Unit Type First Page Root Page First IAM Page
\n\t———– ——– —————– ————— ———- ——— ————–
\n\ttest        1        72057594039762944 IN_ROW_DATA     (1:144)    (1:144)   (1:145) <\/font>\n\t<\/p>\n

\n\tDBCC TRACEON (3604);
\n\tGO
\n\tDBCC PAGE (keyupdatetest, 1, 144, 2);
\n\tGO <\/font>\n\t<\/p>\n

\n\tMemory Dump @0x0000000016A6A000 <\/font>\n\t<\/p>\n

\n\t0000000016A6A000:   01010400 00c20001 00000000 00000800 †…..Â……….
\n\t0000000016A6A010:   00000000 00000100 1c000000 bd174108 †…………½.A.
\n\t0000000016A6A020:   90000000 01000000 1d000000 8a000000 †…………\u008a…
\n\t0000000016A6A030:   12000000 00000000 00000000 239dbd4a †…………#.½J
\n\t0000000016A6A040:   00000000 00000000 00000000 00000000 †…………….
\n\t0000000016A6A050:   00000000 00000000 00000000 00000000 †…………….
\n\t0000000016A6A060:   30000800 01000000 03000002 001100e1 †0…………..á
\n\t0000000016A6A070:   07506175 6c506175 6c506175 6c506175 †.PaulPaulPaulPau
\n\t0000000016A6A080:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000016A6A090:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000016A6A0A0:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000016A6A0B0:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t<snip>
\n\t0000000016A6A820:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000016A6A830:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000016A6A840:   6c000021 21212121 21212121 21212121 †l..!!!!!!!!!!!!!
\n\t0000000016A6A850:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!!
\n\t<snip>
\n\t0000000016A6BFE0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!!
\n\t0000000016A6BFF0:   21212121 21212121 21212121 21216000 †!!!!!!!!!!!!!!`. <\/font>\n\t<\/p>\n

\n\tOFFSET TABLE: <\/font>\n\t<\/p>\n

\n\tRow – Offset                        
\n\t0 (0x0) – 96 (0x60) <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow I'll update the record: <\/font>\n<\/p>\n

\n

\n\tUPDATE test SET c1 = 2 WHERE c1 =1;
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd let's look at the log records generated (in the output below I've removed all the extraneous stuff from the log that occurs before my transaction): <\/font>\n<\/p>\n

\n

\n\tSELECT [Current LSN], [Operation], [Context],
\n\t [Log Record Length], [Page ID], [Slot ID] FROM fn_dblog (NULL, NULL);
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\n

\n\tCurrent LSN             Operation           Context               Log Record Length Page ID        Slot ID
\n\t———————– ——————- ——————— —————– ————– ———–
\n\t0000001d:00000096:0011  LOP_BEGIN_XACT      LCX_NULL              112               NULL           NULL
\n\t0000001d:00000096:0012  LOP_DELETE_ROWS     LCX_MARK_AS_GHOST     2116              0001:00000090  0
\n\t0000001d:00000096:0013  LOP_MODIFY_HEADER   LCX_PFS               76                0001:00000001  0
\n\t0000001d:00000096:0014  LOP_SET_BITS        LCX_PFS               56                0001:00000001  0
\n\t0000001d:00000096:0015  LOP_INSERT_ROWS     LCX_CLUSTERED         2120              0001:00000090  1
\n\t0000001d:00000096:0016  LOP_COMMIT_XACT     LCX_NULL              52                NULL           NULL
\n\t0000001d:000000a2:0001  LOP_EXPUNGE_ROWS    LCX_CLUSTERED         64                0001:00000090  0
\n\t0000001d:000000a2:0002  LOP_SET_BITS        LCX_PFS               56                0001:00000001  0
\n\t0000001d:000000a2:0003  LOP_MODIFY_HEADER   LCX_PFS               76                0001:00000001  0 <\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe row is deleted and then inserted. Look at the log record lengths! And look at the slot numbers. The new row is in slot 1 (the LOP_INSERT_ROWS<\/font> record) and slot 0 is ghosted (the LOP_DELETE_ROWS<\/font> record), and then we see ghost cleanup running (the LOP_EXPUNGE_ROWS<\/font> record), which removes the old slot 0, and the slot array shifts down so the new record is slot 0 again. See my blog post Inside the Storage Engine: Ghost cleanup in depth<\/a> for info on ghost cleanup internals.<\/font>\n<\/p>\n

\nBut is it in the same physical place on the page? No – a whole new record was created. <\/font>\n<\/p>\n

\n

\n\tDBCC PAGE (keyupdatetest, 1, 144, 2);
\n\tGO <\/font>\n\t<\/p>\n

\n\tMemory Dump @0x0000000013CAC000 <\/font>\n\t<\/p>\n

\n\t0000000013CAC000:   01010400 00800001 00000000 00000800 †…………….
\n\t0000000013CAC010:   00000000 00000100 1c000000 bd172210 †…………½.".
\n\t0000000013CAC020:   90000000 01000000 1d000000 a2000000 †…………¢…
\n\t0000000013CAC030:   01000000 76020000 00000000 239dbd4a †….v…….#.½J
\n\t0000000013CAC040:   01000000 00000000 00000000 00000000 †…………….
\n\t0000000013CAC050:   00000000 00000000 00000000 00000000 †…………….
\n\t0000000013CAC060:   3c000800 01000000 03000002 001100e1 †<…………..á
\n\t0000000013CAC070:   07506175 6c506175 6c506175 6c506175 †.PaulPaulPaulPau
\n\t0000000013CAC080:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000013CAC090:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t<snip>
\n\t0000000013CAC820:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000013CAC830:   6c506175 6c506175 6c506175 6c506175 †lPaulPaulPaulPau
\n\t0000000013CAC840:   6c300008 00020000 00030000 02001100 †l0…………..
\n\t0000000013CAC850:   e1075061 756c5061 756c5061 756c5061 †á.PaulPaulPaulPa
\n\t0000000013CAC860:   756c5061 756c5061 756c5061 756c5061 †ulPaulPaulPaulPa
\n\t<snip>
\n\t0000000013CADFE0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!!
\n\t0000000013CADFF0:   21212121 21212121 21212121 41084108 †!!!!!!!!!!!!A.A. <\/font>\n\t<\/p>\n

\n\tOFFSET TABLE: <\/font>\n\t<\/p>\n

\n\tRow – Offset                        
\n\t0 (0x0) – 2113 (0x841)               <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNotice that where the original record ended (offset 0x830<\/font> into the page). In the first page dump, everything after that is empty space. In the second page dump, the new record starts there. And look at the slot array (called the offset table by DBCC PAGE<\/font>). In the first page dump the original record started at offset 0x60<\/font>, and in the second dump it starts at offset 0x841<\/font>. Even though ghost cleanup ran, the record did NOT migrate to it's original location. <\/font>\n<\/p>\n

\nIt's very clear that an in-place update did not happen, and that the entire record is recreated in it's entirety. <\/font>\n<\/p>\n

\nIn this case, the record ended up being physical slot 0 again, but that's a pathological case because there's only one slot on the page. If you repeat the experiment with two records (e.g. c1=1 and c1=2) and then update c1 of the first record to 3, you'll see the new record being physical slot 2, then after ghost cleanup becoming physical slot 1. But again, it's deleted and reinserted in it's entirety. <\/font>\n<\/p>\n

\nNow I'll show you a case where the row has to move to a new page – definitely not an in-place update! <\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE KeyUpdateTest;
\n\tGO
\n\tUSE KeyUpdateTest;
\n\tGO
\n\tALTER DATABASE KeyUpdateTest SET RECOVERY SIMPLE;
\n\tALTER DATABASE KeyUpdateTest SET AUTO_CREATE_STATISTICS OFF;
\n\tALTER DATABASE KeyUpdateTest SET AUTO_UPDATE_STATISTICS OFF;
\n\tGO
\n\tCREATE TABLE test (c1 INT, c2 VARCHAR (4000));
\n\tGO
\n\tCREATE CLUSTERED INDEX test_cl ON test (c1);
\n\tGO
\n\tCHECKPOINT;
\n\tGO
\n\tINSERT INTO test VALUES (1, REPLICATE ('Paul', 1000));
\n\tGO
\n\tINSERT INTO test VALUES (2, REPLICATE ('Erin', 1000));
\n\tGO
\n\tCHECKPOINT;
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\nI've filled up the page with two rows. <\/font>\n<\/p>\n

\n

\n\tDBCC PAGE (keyupdatetest, 1, 144, 2);
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\n

\n\t<snip>
\n\tMetadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
\n\tpminlen = 8                          m_slotCnt = 2                        m_freeCnt = 58
\n\tm_freeData = 8130                    m_reservedCnt = 0                    m_lsn = (29:150:2)
\n\t<snip>
\n\t0000000016A6BFA0:   696e4572 696e4572 696e4572 696e4572 †inErinErinErinEr
\n\t0000000016A6BFB0:   696e4572 696e4572 696e4572 696e4572 †inErinErinErinEr
\n\t0000000016A6BFC0:   696e0000 21212121 21212121 21212121 †in..!!!!!!!!!!!!
\n\t0000000016A6BFD0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!!
\n\t0000000016A6BFE0:   21212121 21212121 21212121 21212121 †!!!!!!!!!!!!!!!!
\n\t0000000016A6BFF0:   21212121 21212121 21212121 11106000 †!!!!!!!!!!!!..`. <\/font>\n\t<\/p>\n

\n\tOFFSET TABLE: <\/font>\n\t<\/p>\n

\n\tRow – Offset                        
\n\t1 (0x1) – 4113 (0x1011)             
\n\t0 (0x0) – 96 (0x60) <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow I'll update the first row to have c1 = 3 so my row moves away from Erin's :-) <\/font>\n<\/p>\n

\n

\n\tUPDATE test SET c1 = 3 WHERE c1 =1;
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd look at the log: <\/font>\n<\/p>\n

\n

\n\tSELECT [Current LSN], [Operation], [Context],
\n\t [Log Record Length], [Page ID], [Slot ID] FROM fn_dblog (NULL, NULL);
\n\tGO<\/p>\n

\tCurrent LSN             Operation           Context                Log Record Length Page ID        Slot ID
\n\t———————– ——————- ———————- —————– ————– ———–
\n\t0000001d:000000a3:0015  LOP_BEGIN_XACT      LCX_NULL               136               NULL           NULL
\n\t0000001d:000000a3:0016  LOP_MODIFY_ROW      LCX_PFS                80                0001:00000001  0
\n\t0000001d:000000a3:0017  LOP_MODIFY_ROW      LCX_IAM                88                0001:00000091  0
\n\t0000001d:000000a3:0018  LOP_HOBT_DELTA      LCX_NULL               64                NULL           NULL
\n\t0000001d:000000a3:0019  LOP_FORMAT_PAGE     LCX_INDEX_INTERIOR     84                0001:00000092  -1
\n\t0000001d:000000a3:001a  LOP_INSERT_ROWS     LCX_INDEX_INTERIOR     84                0001:00000092  0
\n\t0000001d:000000a3:001b  LOP_ROOT_CHANGE     LCX_CLUSTERED          96                0001:00000082  52
\n\t0000001d:000000a3:001c  LOP_COMMIT_XACT     LCX_NULL               52                NULL           NULL
\n\t0000001d:000000a3:001d  LOP_BEGIN_XACT      LCX_NULL               120               NULL           NULL
\n\t0000001d:000000a3:001e  LOP_MODIFY_ROW      LCX_PFS                80                0001:00000001  0
\n\t0000001d:000000a3:001f  LOP_MODIFY_ROW      LCX_IAM                88                0001:00000091  0
\n\t0000001d:000000a3:0020  LOP_HOBT_DELTA      LCX_NULL               64                NULL           NULL
\n\t0000001d:000000a3:0021  LOP_FORMAT_PAGE     LCX_HEAP               84                0001:00000093  -1
\n\t0000001d:000000a3:0022  LOP_INSERT_ROWS     LCX_CLUSTERED          4092              0001:00000093  0
\n\t0000001d:000000a3:0023  LOP_DELETE_SPLIT    LCX_CLUSTERED          60                0001:00000090  1
\n\t0000001d:000000a3:0024  LOP_MODIFY_HEADER   LCX_HEAP               84                0001:00000090  0
\n\t0000001d:000000a3:0025  LOP_INSERT_ROWS     LCX_INDEX_INTERIOR     88                0001:00000092  1
\n\t0000001d:000000a3:0026  LOP_COMMIT_XACT     LCX_NULL               52                NULL           NULL
\n\t0000001d:000000a3:0027  LOP_INSERT_ROWS     LCX_CLUSTERED          4120              0001:00000093  1
\n\t0000001d:000000a3:0028  LOP_COMMIT_XACT     LCX_NULL               52                NULL           NULL <\/font>\n\t<\/p>\n<\/blockquote>\n

\nThis is really interesting. Here's what happens (missing a few things for clarity): <\/font>\n<\/p>\n

    \n
  • \n
    \n\t0000001d:000000a3:0016<\/font>: page (1:146)<\/font> is allocated to be the new root page for the clustered index <\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:0019<\/font>: it gets minimally formatted (just the page header – although the page header is 96 bytes, there is 12 bytes of empty space at the end) <\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:001a<\/font>: the index record pointing at (1:144)<\/font> is inserted in the root page<\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:001b<\/font>: the index root os changedd from (1:144)<\/font> to (1:146)<\/font> in metadata <\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:001e<\/font>: page (1:147)<\/font> is allocated to be the second data page at the leaf of the clustered index<\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:0022<\/font>: a page split of page (1:144)<\/font> occurs, moving the Erin row to slot 0 of page (1:147)<\/font><\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:0023<\/font>: the Erin row is removed from page (1:144)<\/font> – not ghosted<\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:0025<\/font>: the index record pointing at (1:147)<\/font> is inserted in the root page<\/font>\n\t<\/div>\n<\/li>\n
  • \n
    \n\t0000001d:000000a3:0027<\/font>: the updated Paul row is inserted as slot 1 of page (1:147)<\/font><\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n

    \nAs you can see, this is as far as you can get from doing an in-place update. Page (1:144)<\/font> is left with a single ghost-record on (the Paul record – the Erin record isn't ghosted because it was moved because of a split). The next access to the page will cause ghost cleanup to remove it and deallocate the page.<\/font>\n<\/p>\n

    \nSo no, in-place updates of key values do not happen, even though they appear to at first glance. You can experiment to show the same thing happening in nonclustered indexes too.<\/font>\n<\/p>\n

    \nAnother misconception busted!<\/font>\n<\/p>\n

    \nPS Some of you may remember when such in-place updates really *did* happen. That was before 2005 when the Storage Engine (specifically my old Access Methods team) did the index maintenance (changing clustered and nonclustered index structures based on key value changes). A whole bunch of features in 2005 meant that we switched it over to the Query Processor to drive the index maintenance, and nifty short-cuts like we used to do aren't possible any more.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

    There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,48,59,62],"tags":[],"class_list":["post-602","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-inside-the-storage-engine","category-mcm","category-on-disk-structures"],"yoast_head":"\nDo changes to index keys really do in-place updates? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Do changes to index keys really do in-place updates? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-02-25T09:22:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:53:37+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/\",\"name\":\"Do changes to index keys really do in-place updates? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-02-25T09:22:00+00:00\",\"dateModified\":\"2017-04-13T16:53:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Do changes to index keys really do in-place updates?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Do changes to index keys really do in-place updates? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/","og_locale":"en_US","og_type":"article","og_title":"Do changes to index keys really do in-place updates? - Paul S. Randal","og_description":"There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/","og_site_name":"Paul S. Randal","article_published_time":"2011-02-25T09:22:00+00:00","article_modified_time":"2017-04-13T16:53:37+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/","name":"Do changes to index keys really do in-place updates? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-02-25T09:22:00+00:00","dateModified":"2017-04-13T16:53:37+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-changes-to-index-keys-really-do-in-place-updates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Do changes to index keys really do in-place updates?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=602"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/602\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}