{"id":1164,"date":"2007-10-05T00:52:51","date_gmt":"2007-10-05T00:52:51","guid":{"rendered":"\/blogs\/paul\/post\/Indexes-From-Every-Angle-What-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx"},"modified":"2017-07-04T09:06:01","modified_gmt":"2017-07-04T16:06:01","slug":"indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/","title":{"rendered":"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?"},"content":{"rendered":"<p style=\"text-align: justify;\">I&#8217;d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums.<\/p>\n<p style=\"text-align: justify;\">What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well &#8211; it depends! (my favorite answer :-)).<\/p>\n<p style=\"text-align: justify;\">Before we get into that discussion, I\u2019ll give you a little background.<\/p>\n<p style=\"text-align: justify;\">One way to describe a non-clustered index is whether it is a <em>covering index<\/em> or not. A covering index is one which has all the table columns necessary to satisfy a query, and so there is no need to go back to the underlying table (which I\u2019ll call the <em>base table<\/em>) to fetch additional columns. Most non-clustered indexes are not covering indexes and so it is necessary for each non-clustered index record (even in covering indexes) to include a link back to the corresponding base table record. This link takes various forms, depending on the structure of the base table:<\/p>\n<ul style=\"text-align: justify;\">\n<li style=\"text-align: justify;\"><b>Heap:<\/b> A heap does not impose any logical ordering on the records within it, so the only way to find a particular record within a heap is to do a full table scan or to know the physical location of the record (i.e. which record slot on which data page) \u2013 called the <i>physical record ID<\/i> or <i>physical RID<\/i>. Clearly doing a full table scan every time a query needs to go from a non-clustered index record back to a heap is not feasible, so each non-clustered index record stores the physical RID of the corresponding heap record.<\/li>\n<li style=\"text-align: justify;\"><b>Clustered index:<\/b> A clustered index does impose a logical ordering on the records within it and the ordering exactly matches the definition of the clustered index keys \u2013 known as the <i>cluster key<\/i>. The fastest way to find a particular record in a clustered index is to use the cluster key to navigate through the clustered index b-tree to find the exact record. So, non-clustered indexes over a clustered index include the cluster key of the base table record in each non-clustered index record. This is known as the <em>logical <\/em>RID. There is a twist though, depending on whether the clustered index is unique or not.<\/li>\n<li style=\"text-align: justify;\"><strong>Non-unique clustered index:<\/strong> Every record in a clustered index HAS to be unique, otherwise there would be no way to deterministically navigate to a particular record using the index b-tree. In a non-unique clustered index, SQL Server has to add a special column, called the <em>uniquifier<\/em>, to each record, so that if multiple records have the same cluster key values, the uniquifier column will be the tie-breaker. This uniquifier column is added as part of the cluster key, and so it is also present in all non-clustered index records as part of the logical RID.<\/li>\n<li style=\"text-align: justify;\"><strong>Unique clustered index:<\/strong> Every record in a unique clustered index is already unique and so no extra column is required in the cluster key.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Ok\u00a0&#8211; background out of the way. Now let&#8217;s discuss what happens to non-clustered indexes when various actions are performed to the base table.<\/p>\n<p style=\"text-align: justify;\"><strong>SQL Server 2000<\/strong><\/p>\n<ul style=\"text-align: justify;\">\n<li style=\"text-align: justify;\"><strong>Going from a heap to a clustered index:<\/strong> Changing the fundamental structure of a table from a heap to a clustered index means you&#8217;re changing the way that records can be looked up in the table, from physical to logical. This means that all the physical RIDs that are stored in the non-clustered index records need to be changed to logical RIDs. So &#8211; in this case, all non-clustered <span style=\"font-family: Verdana;\">indexes are rebuilt after the clustered index has been built.<\/span><\/li>\n<li style=\"text-align: justify;\"><strong>Going from a clustered index to a heap: <\/strong>This is the opposite of the case above &#8211; you&#8217;re changing the way records are looked up from logical to physical. This means that all the logical RIDs that are stored in the non-clustered index records need to be changed to physical RIDs. So &#8211; in this case, all non-clustered indexes are rebuilt after the clustered index has been dropped.<\/li>\n<li style=\"text-align: justify;\"><strong>Rebuilding a unique clustered index: <\/strong>This operation has no effect on the cluster key, so there is no need for the non-clustered indexes to be rebuilt. Various early Service Packs of SQL Server 2000 had bugs that caused this behavior to be broken &#8211; and this is the cause of much of the confusion around this behavior.<\/li>\n<li style=\"text-align: justify;\"><strong>Rebuilding a non-unique clustered index:<\/strong> In SQL Server 2000, when a non-unique clustered index (which contains uniquifier columns) is rebuilt, all the uniquifier values are regenerated. This essentially means that the cluster keys have changed and so all the non-clustered indexes\u00a0are rebuilt after the clustered index rebuild has completed.<\/li>\n<li style=\"text-align: justify;\"><strong>Changing the clustered index schema: <\/strong>This is simple &#8211; any change to the clustered index schema that changes the cluster keys will cause all the non-clustered indexes to be rebuilt.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><strong>SQL Server 2005 and later<\/strong><\/p>\n<ul style=\"text-align: justify;\">\n<li style=\"text-align: justify;\"><strong>Going from a heap to a clustered index: <\/strong>This is the same as SQL Server 2000 &#8211; all non-clustered indexes are rebuilt, regardless of whether the clustered index was created online or offline.<\/li>\n<li style=\"text-align: justify;\"><strong>Going from a clustered index to a heap: <\/strong>Again, all non-clustered indexes are rebuilt, regardless of whether the clustered index is dropped online or offline.<\/li>\n<li style=\"text-align: justify;\"><strong>Rebuilding a unique clustered index: <\/strong>This is the same as SQL Server 2000 &#8211; the cluster keys aren&#8217;t changing and so the non-clustered indexes are not rebuilt.<\/li>\n<li style=\"text-align: justify;\"><strong>Rebuilding a non-unique clustered index:<\/strong> Aha! This is different from SQL Server 2000. SQL Server 2005 will RE-USE the old uniquifier values so the cluster keys don&#8217;t change. This means that non-clustered indexes are NOT rebuilt in this case &#8211; that&#8217;s very cool!<\/li>\n<li><strong>Changing the clustered index schema:<\/strong>\n<ul>\n<li style=\"text-align: justify;\"><strong>Changing the cluster key: <\/strong>This behavior has to be the same as SQL Server 2000 &#8211; any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created.<\/li>\n<li style=\"text-align: justify;\"><strong>Changing anything else (e.g. partitioning it or moving it):<\/strong>This is one of the cases that confuses people in SQL Server 2005. Moving it to a different filegroup doesn&#8217;t change the cluster key at all &#8211; so there&#8217;s no need to rebuild the non-clustered indexes, but applying a partitioning scheme DOES cause the nonclustered indexes to be rebuilt.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">So the rule of thumb to remember is: if the physical or logical RID contained in the non-clustered index records needs\u00a0to change\u00a0then all the non-clustered indexes need to be rebuilt. It&#8217;s as simple as that.<\/p>\n<p style=\"text-align: justify;\">Hopefully this will help to clear up some confusion &#8211; let me know of any questions!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well &#8211; it [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,62],"tags":[],"class_list":["post-1164","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-on-disk-structures"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - 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\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I&#8217;d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well &#8211; it [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-05T00:52:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-04T16:06:01+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=\"5 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\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\",\"name\":\"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-05T00:52:51+00:00\",\"dateModified\":\"2017-07-04T16:06:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?\"}]},{\"@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":"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - 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\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/","og_locale":"en_US","og_type":"article","og_title":"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - Paul S. Randal","og_description":"I&#8217;d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well &#8211; it [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-05T00:52:51+00:00","article_modified_time":"2017-07-04T16:06:01+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/","name":"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-05T00:52:51+00:00","dateModified":"2017-07-04T16:06:01+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?"}]},{"@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\/1164","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=1164"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1164\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}