{"id":1139,"date":"2007-11-01T02:05:57","date_gmt":"2007-11-01T02:05:57","guid":{"rendered":"\/blogs\/paul\/post\/Indexes-From-Every-Angle-What-indexes-are-necessary-when-using-SWITCH-PARTITION.aspx"},"modified":"2017-04-13T09:52:05","modified_gmt":"2017-04-13T16:52:05","slug":"indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/","title":{"rendered":"Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION?"},"content":{"rendered":"


\n

This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005<\/a> – and is implementing a “sliding-window<\/EM>” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into\/from a partitioned production table. Insertion is done by taking a table and making it a new partition of the production table – called switching-in<\/EM>. Deletion is done by removing a partition from the production table and making it into a stand-alone table – called switching-out<\/EM>.)<\/P>
\n

The question is – what indexes are required on the staging table to prevent the ALTER TABLE … SWITCH PARTITION<\/FONT> statement from failing with a message like that below:<\/FONT><\/P>
\n


\n

Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘PartitionTest.dbo.StagingTable’ for the index ‘NC_Birthday’ in target table ‘PartitionTest.dbo.ProductionTable’.<\/FONT><\/SPAN><\/P><\/BLOCKQUOTE>
\n

The answer is that the staging table has to have the exact<\/EM> same indexes – clustered and non-clustered – as the production table. I asked Kimberly if it has to have the same constraints too – the answer is yes, plus the staging table has to have a trusted constraint on it such that SQL Server can tell (without checking all the data in the staging table) that all the data satisfies the partitioning function for the partition that you’re switching-in (i.e. the partition that the staging table will become in the production table). If it doesn’t, the switching-in will fail with the following error:<\/P>
\n


\n

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘PartitionTest.dbo.StagingTable’ allow values that are not allowed by range defined by partition 4 on target table ‘PartitionTest.dbo.ProductionTable’.<\/FONT><\/FONT><\/SPAN><\/P><\/BLOCKQUOTE>
\n

One thing that confuses people is that SQL Server does not create the target table for you when doing a switch-out of a partition. The target table has to exist and have the exact same schema as the production table. Also, it has to be completely empty – otherwise you’ll get an error like:<\/P>
\n


\n

Msg 4905, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The target table ‘PartitionTest.dbo.StagingTable’ must be empty.<\/FONT><\/SPAN><\/P><\/BLOCKQUOTE>
\n

The must-be-empty requirement also holds for switching-in operations – the partition that will be created has to be empty otherwise a similar 4904 error results.<\/FONT><\/SPAN><\/P>
\n

Hope this helps!<\/FONT><\/P><\/SPAN><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005 – and is implementing a “sliding-window” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into\/from a partitioned production table. Insertion […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,65],"tags":[],"class_list":["post-1139","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-partitioning"],"yoast_head":"\nIndexes From Every Angle: What indexes are necessary when using SWITCH PARTITION? - 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-indexes-are-necessary-when-using-switch-partition\/\" \/>\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 indexes are necessary when using SWITCH PARTITION? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005 – and is implementing a “sliding-window” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into\/from a partitioned production table. Insertion […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-11-01T02:05:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16: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=\"2 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-indexes-are-necessary-when-using-switch-partition\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/\",\"name\":\"Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-11-01T02:05:57+00:00\",\"dateModified\":\"2017-04-13T16:52:05+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-indexes-are-necessary-when-using-switch-partition\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/#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 indexes are necessary when using SWITCH PARTITION?\"}]},{\"@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 indexes are necessary when using SWITCH PARTITION? - 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-indexes-are-necessary-when-using-switch-partition\/","og_locale":"en_US","og_type":"article","og_title":"Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION? - Paul S. Randal","og_description":"This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005 – and is implementing a “sliding-window” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into\/from a partitioned production table. Insertion […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/","og_site_name":"Paul S. Randal","article_published_time":"2007-11-01T02:05:57+00:00","article_modified_time":"2017-04-13T16:52:05+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/","name":"Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-11-01T02:05:57+00:00","dateModified":"2017-04-13T16:52:05+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-indexes-are-necessary-when-using-switch-partition\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-indexes-are-necessary-when-using-switch-partition\/#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 indexes are necessary when using SWITCH PARTITION?"}]},{"@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\/1139","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=1139"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1139\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}