{"id":2462,"date":"2014-07-02T11:05:33","date_gmt":"2014-07-02T15:05:33","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2462"},"modified":"2014-11-13T13:37:01","modified_gmt":"2014-11-13T18:37:01","slug":"intermediate-index-pages","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/","title":{"rendered":"Intermediate Index Pages in SQL Server"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSQL Server organizes all conventional indexes on disk in a structure called B+ Tree<\/a>. In a B+ Tree two types of nodes exist. The first type is the leaf node containing the actual data. This node type is represented in SQL Server by type-1 data pages<\/a>.\n<\/p>\n

\nThe second type is the intermediate level node. This node contains only key values and pointers to the next level. In SQL Server, these nodes are represented by type-2 pages, the intermediate index pages.\n<\/p>\n

An Intermediate Index Page Example<\/h3>\n

\nThe way to look at intermediate index pages in SQL Server is very similar to all other pages. First we need to set trace flag 3604 and then we can use DBCC PAGE<\/span>:\n<\/p>\n

\n[sql]\nDBCC TRACEON(3604);
\nDBCC PAGE(0,1,283,3);
\n[\/sql]\n<\/div>\n

\nThe image below shows us that this page is, as expected, of type 2:\n<\/p>\n

\n\"The<\/a>\n<\/p>\n

\nHowever, there is one difference to other DBCC PAGE<\/span> results. There is an actual result set, returned by this statement. Let us take a closer look:\n<\/p>\n

\n\"Rows<\/a>\n<\/p>\n

\nThe first three columns indicate which entry on page 1:283 we are actually looking at. The Level<\/span> column tells us which level of the index we are at. Level 1<\/span> means we are actually looking at the root page. The first level in a B+ Tree always consists of only a single node. That node is called the root node, or - in SQL Server - root page.\n<\/p>\n

The next two columns, ChildFileId<\/span> and ChildPageId<\/span> together make up the pointer to the child page. Remember, each entry in an intermediate index page (in a B+ Tree) points to another page in the index, its child page.\n<\/p>\n

\nid<\/span> is the actual key column. If the index key had multiple columns, all would be part of this result set. If you look at the entry for id = 7<\/span>, you can see that it points to child page 285. The next entry is id = 10<\/span>. So we would expect to find values that are >= 7<\/span> and <10[\/tt] on page 285. Let us take a look: \t\n<\/p>\n

\n\"sys.fn_PhysLocCracker<\/a>\n<\/p>\n

\nSelecting from the table using the undocumented [tt]%%physloc%%<\/span> virtual column<\/a>, we can see that page 285 indeed contains the values 7<\/span>, 8<\/span> and 9<\/span>, so exactly the values we expected.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server indexes are organized as B+ Trees on disk. Each node in such a tree is a single SQL Server data page. The leaf level nodes are normal data pages of type 1, while the intermediate index pages are of type 2.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server stores indexes in B+ Tree format on disk. Each node is represented by a SQL Server page. The actual rows are stored in data pages while the index structure resides in intermediate index pages. Read on to find out more.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2463,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,27,14,105],"tags":[209,208,107,114,15,108,109],"class_list":["post-2462","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","category-series","category-sql-server-internals","category-storage-wednesday","tag-data-page","tag-intermediate-index-page","tag-page","tag-page-header","tag-sql-server","tag-storage","tag-storage-engine"],"acf":[],"yoast_head":"\nIntermediate Index Pages in SQL Server - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Intermediate Index Pages in SQL Server - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-02T15:05:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:37:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"468\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Intermediate Index Pages in SQL Server\",\"datePublished\":\"2014-07-02T15:05:33+00:00\",\"dateModified\":\"2014-11-13T18:37:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/\"},\"wordCount\":347,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/index_page.jpg\",\"keywords\":[\"data page\",\"intermediate index page\",\"page\",\"Page Header\",\"SQL Server\",\"storage\",\"storage engine\"],\"articleSection\":[\"General\",\"Series\",\"SQL Server Internals\",\"Storage Wednesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/\",\"name\":\"Intermediate Index Pages in SQL Server - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/index_page.jpg\",\"datePublished\":\"2014-07-02T15:05:33+00:00\",\"dateModified\":\"2014-11-13T18:37:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/index_page.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/index_page.jpg\",\"width\":768,\"height\":468,\"caption\":\"Rows on an intermediate index page.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2462\\\/intermediate-index-pages\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Intermediate Index Pages in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Intermediate Index Pages in SQL Server - sqlity.net","description":"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.","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:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/","og_locale":"en_US","og_type":"article","og_title":"Intermediate Index Pages in SQL Server - sqlity.net","og_description":"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.","og_url":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-07-02T15:05:33+00:00","article_modified_time":"2014-11-13T18:37:01+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Intermediate Index Pages in SQL Server","datePublished":"2014-07-02T15:05:33+00:00","dateModified":"2014-11-13T18:37:01+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/"},"wordCount":347,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","keywords":["data page","intermediate index page","page","Page Header","SQL Server","storage","storage engine"],"articleSection":["General","Series","SQL Server Internals","Storage Wednesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/","url":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/","name":"Intermediate Index Pages in SQL Server - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","datePublished":"2014-07-02T15:05:33+00:00","dateModified":"2014-11-13T18:37:01+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server stores indexes in B+ Tree format on disk. Find out how the B+ Tree structure is built and what different page types are involved.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","width":768,"height":468,"caption":"Rows on an intermediate index page."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2462\/intermediate-index-pages\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Intermediate Index Pages in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/index_page.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-DI","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2462"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2462\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2463"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}