{"id":1166,"date":"2007-10-04T15:29:00","date_gmt":"2007-10-04T15:29:00","guid":{"rendered":"\/blogs\/paul\/post\/Inside-the-Storage-Engine-Proof-that-records-are-not-always-physically-stored-in-index-key-order.aspx"},"modified":"2017-06-10T10:52:05","modified_gmt":"2017-06-10T17:52:05","slug":"inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/","title":{"rendered":"Inside the Storage Engine: Proof that records are not always physically stored in index key order"},"content":{"rendered":"<p style=\"text-align: justify;\">I mentioned this in my <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-anatomy-of-a-page\/\">Anatomy of a page<\/a> post &#8211; its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here&#8217;s proof for you that this is incorrect (as well as introducing you to the other dump styles for <em>DBCC PAGE<\/em>).<\/p>\n<p style=\"text-align: justify;\">I&#8217;m going to create a table with a clustered index on an integer column and keep the table to a single page for simplicity:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\nIF DATABASEPROPERTY (N'rowordertest', 'Version') &amp;gt; 0 DROP DATABASE &#x5B;rowordertest];\r\nGO\r\n\r\nCREATE DATABASE &#x5B;rowordertest];\r\nGO\r\nUSE &#x5B;rowordertest];\r\nGO\r\n\r\nCREATE TABLE &#x5B;t1] (&#x5B;c1] INT, &#x5B;c2] VARCHAR (10));\r\nCREATE CLUSTERED INDEX &#x5B;t1c1] ON &#x5B;t1] (&#x5B;c1]);\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">Now I&#8217;m going to insert a few rows into the table, with <em>c1<\/em> from 2 to 5, deliberately\u00a0not inserting <em>c1<\/em> = 1:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nINSERT INTO &#x5B;t1] VALUES (2, REPLICATE ('b', 10));\r\nINSERT INTO &#x5B;t1] VALUES (3, REPLICATE ('c', 10));\r\nINSERT INTO &#x5B;t1] VALUES (4, REPLICATE ('d', 10));\r\nINSERT INTO &#x5B;t1] VALUES (5, REPLICATE ('e', 10));\r\nGO\r\n<\/pre>\n<p>Now, using <em>DBCC IND<\/em> we see that the data page is <em>(1:143)<\/em> and dumping that with <em>DBCC PAGE<\/em> gives the following (skipping the header output):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC IND ('rowordertest', 't1', 1);\r\nGO\r\n\r\nDBCC TRACEON (3604);\r\nGO\r\n\r\nDBCC PAGE ('rowordertest', 1, 143, 3);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSlot 0 Offset 0x60 Length 27\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS\r\n\r\nMemory Dump @0x5BA3C060\r\n00000000:   30000800 02000000 0300f802 0011001b  0...............\r\n00000010:   00626262 62626262 626262             .bbbbbbbbbb\r\nUNIQUIFIER = &#x5B;NULL]\r\n\r\nSlot 0 Column 1 Offset 0x4 Length 4\r\nc1 = 2\r\n\r\nSlot 0 Column 2 Offset 0x11 Length 10\r\nc2 = bbbbbbbbbb\r\n\r\n(skip slots 2 and 3 for brevity)\r\n\r\nSlot 3 Offset 0xb1 Length 27\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS\r\n\r\nMemory Dump @0x5BA3C0B1\r\n00000000:   30000800 05000000 0300f802 0011001b  0...............\r\n00000010:   00656565 65656565 656565             .eeeeeeeeee\r\nUNIQUIFIER = &#x5B;NULL]\r\n\r\nSlot 3 Column 1 Offset 0x4 Length 4\r\nc1 = 5\r\n\r\nSlot 3 Column 2 Offset 0x11 Length 10\r\nc2 = eeeeeeeeee\r\n<\/pre>\n<p style=\"text-align: justify;\"><em>DBCC PAGE<\/em> with dump-style 3 always outputs the records on a page in their logical order (because that&#8217;s how the slot array is ordered). Notice that the record with <em>c1<\/em> = 2 is stored at offset <em>0x60<\/em> in the page and the last record on the page with <em>c1<\/em> = 5 is stored at offset <em>0xb1<\/em>. Now we&#8217;ll insert a record with <em>c1<\/em> = 1. This will become the first logical record in the index, but will it cause the page to be shuffled so the records can all be <em>stored<\/em> in logical order?<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nINSERT INTO &#x5B;t1] VALUES (1, REPLICATE ('a', 10));\r\nGO\r\n\r\nDBCC PAGE ('rowordertest', 1, 143, 3);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSlot 0 Offset 0xcc Length 27\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS\r\n\r\nMemory Dump @0x61FCC0CC\r\n00000000:   30000800 01000000 0300f802 0011001b  0...............\r\n00000010:   00616161 61616161 616161             .aaaaaaaaaa\r\nUNIQUIFIER = &#x5B;NULL]\r\n\r\nSlot 0 Column 1 Offset 0x4 Length 4\r\nc1 = 1\r\n\r\nSlot 0 Column 2 Offset 0x11 Length 10\r\nc2 = aaaaaaaaaa\r\n\r\nSlot 1 Offset 0x60 Length 27\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS\r\n\r\n(snip)\r\n<\/pre>\n<p style=\"text-align: justify;\">The answer is no. Even though the record with <em>c1<\/em> = 1 is output by <em>DBCC PAGE<\/em> first, look at its offset within the page &#8211; <em>0xCC<\/em> &#8211; clearly the last record on the page and stored in a different physical order than the logical order defined by the index key. Further proof can be obtained by looking at a raw hex dump of the page using dump style 2 with <em>DBCC PAGE<\/em>:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC PAGE ('rowordertest', 1, 143, 2);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n(snip)\r\n\r\n6204C000:   01010400 00400001 00000000 00000800  .....@..........\r\n6204C010:   00000000 00000500 44000000 0f1fe700  ........D.......\r\n6204C020:   8f000000 01000000 13000000 60000000  ............`...\r\n6204C030:   16000000 00000000 00000000 00000000  ................\r\n6204C040:   01000000 00000000 00000000 00000000  ................\r\n6204C050:   00000000 00000000 00000000 00000000  ................\r\n6204C060:   30000800 02000000 0300f802 0011001b  0...............\r\n6204C070:   00626262 62626262 62626230 00080003  .bbbbbbbbbb0....\r\n6204C080:   00000003 00f80200 11001b00 63636363  ............cccc\r\n6204C090:   63636363 63633000 08000400 00000300  cccccc0.........\r\n6204C0A0:   f8020011 001b0064 64646464 64646464  .......ddddddddd\r\n6204C0B0:   64300008 00050000 000300f8 02001100  d0..............\r\n6204C0C0:   1b006565 65656565 65656565 30000800  ..eeeeeeeeee0...\r\n6204C0D0:   01000000 0300f802 0011001b 00616161  .............aaa\r\n6204C0E0:   61616161 61616100 00000000 00000000  aaaaaaa.........\r\n6204C0F0:   00000000 00000000 00000000 00000000  ................\r\n\r\n(snip)\r\n<\/pre>\n<p style=\"text-align: justify;\">You can clearly see that the last row I inserted, with <em>c1<\/em> = 1 and the replicated &#8216;a&#8217;s is stored after the other records on the page, even though its key is logically before the others. And just to nail the point home, I&#8217;ll use <em>DBCC PAGE<\/em> to look at the slot array:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC PAGE ('rowordertest', 1, 143, 1);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n(snip)\r\n\r\nOFFSET TABLE:\r\n\r\nRow - Offset\r\n4 (0x4) - 177 (0xb1)\r\n3 (0x3) - 150 (0x96)\r\n2 (0x2) - 123 (0x7b)\r\n1 (0x1) - 96 (0x60)\r\n0 (0x0) - 204 (0xcc)\r\n\r\n(snip)\r\n<\/pre>\n<p style=\"text-align: justify;\">The slot array grows backwards, which is why its dumped in what looks like reverse logical order. You can see that slot 0, which represents the first logical record on the page, is stored at an offset greater than the others.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I mentioned this in my Anatomy of a page post &#8211; its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here&#8217;s proof for you that this is incorrect (as well as introducing you to the other dump styles [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,48,62,100],"tags":[],"class_list":["post-1166","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-inside-the-storage-engine","category-on-disk-structures","category-undocumented-commands"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Inside the Storage Engine: Proof that records are not always physically stored in index key order - 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\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Inside the Storage Engine: Proof that records are not always physically stored in index key order - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I mentioned this in my Anatomy of a page post &#8211; its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here&#8217;s proof for you that this is incorrect (as well as introducing you to the other dump styles [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-04T15:29:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-10T17:52:05+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=\"4 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\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/\",\"name\":\"Inside the Storage Engine: Proof that records are not always physically stored in index key order - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-04T15:29:00+00:00\",\"dateModified\":\"2017-06-10T17:52:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inside the Storage Engine: Proof that records are not always physically stored in index key order\"}]},{\"@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":"Inside the Storage Engine: Proof that records are not always physically stored in index key order - 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\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/","og_locale":"en_US","og_type":"article","og_title":"Inside the Storage Engine: Proof that records are not always physically stored in index key order - Paul S. Randal","og_description":"I mentioned this in my Anatomy of a page post &#8211; its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here&#8217;s proof for you that this is incorrect (as well as introducing you to the other dump styles [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-04T15:29:00+00:00","article_modified_time":"2017-06-10T17:52:05+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/","name":"Inside the Storage Engine: Proof that records are not always physically stored in index key order - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-04T15:29:00+00:00","dateModified":"2017-06-10T17:52:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-proof-that-records-are-not-always-physically-stored-in-index-key-order\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Inside the Storage Engine: Proof that records are not always physically stored in index key order"}]},{"@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\/1166","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=1166"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1166\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}