{"id":7930,"date":"2016-05-12T20:00:58","date_gmt":"2016-05-12T18:00:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/"},"modified":"2016-05-12T20:00:58","modified_gmt":"2016-05-12T18:00:58","slug":"stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","title":{"rendered":"Stored procedure execution may fail with nonclustered columnstore indexes"},"content":{"rendered":"<p>In this blog post, let\u2019s talk about a weird situation where I faced a nonclustered columnstore issue with SQL Server 2012. Let\u2019s say we are in the same context than this previous blog post <a href=\"http:\/\/dbi-services.com\/blog\/columnstore-index-rebuild-cannot-start\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>. So as a reminder, we decided to benefit from the columnstore index feature after installing a new SQL Server 2012 based infrastructure at my customer shop. As showed in the previous blog, implementing columnstore indexes may introduce some challenges and I will show you a next one here.<\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">Adding several nonclustered columnstore indexes to the existing fact tables led to modify some related ETL stored procedures. Indeed, columnstore indexes shipped with the SQL Server 2012 version are read-only and require to disable them before loading data into the underlying tables. Thus, we obviously added the corresponding DDL statements (disabling + enabling indexes) into the concerned stored procedure between the specific load data statements, basically as following:<\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">ALTER INDEX \u2026 DISABLE\n\n-- load data statement\n\nALTER INDEX \u2026 REBUILD<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">But the first execution of the stored procedure led to the following error message:<\/span><\/p>\n<p><em><strong>Msg 35330, Level 15, State 1, Procedure XXXX, Line 35<\/strong><\/em><br \/>\n<em><strong> INSERT statement failed because data cannot be updated in a table with a columnstore index.<\/strong><\/em><br \/>\n<em><strong> Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.<\/strong><\/em><\/p>\n<p>After double checking the script, I tried to manually execute the code inside the procedure and no issue occurred in this case. In addition, running separately the stored procedure only with the data load routines and the index DDL statements was successfull. So regarding the preceding tests,\u00a0I understood that the problem lies elsewhere. Indeed after some investigations, we found out that this error is basically a side effect of batch compilation because SQL Server compiles the statements of a batch into a single executable unit. So in our case, the load data statement is compiled before the columnstore index is really disabled. You may also read the following microsoft <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/807557\/parser-cannot-determine-that-columnstore-index-is-dropped-before-a-table-update-in-a-stored-procedure\" target=\"_blank\" rel=\"noopener noreferrer\">connect item<\/a> to get a good explanation of this issue as well as some workarounds provided by <em>Vassilis Papadimos<\/em>. In my specific context we chosen to add the RECOMPILE hint to each data loading batch inside the procedure.<\/p>\n<p>Happy development with columnstore indexes!<\/p>\n<p>By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, let\u2019s talk about a weird situation where I faced a nonclustered columnstore issue with SQL Server 2012. Let\u2019s say we are in the same context than this previous blog post here. So as a reminder, we decided to benefit from the columnstore index feature after installing a new SQL Server 2012&hellip;&nbsp;<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[99],"tags":[579,526],"class_list":["post-7930","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-column-store","tag-datawarehouse"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.4 (Yoast SEO v28.7) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Stored procedure execution may fail with nonclustered columnstore indexes<\/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.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Stored procedure execution may fail with nonclustered columnstore indexes\" \/>\n<meta property=\"og:description\" content=\"In this blog post, let\u2019s talk about a weird situation where I faced a nonclustered columnstore issue with SQL Server 2012. Let\u2019s say we are in the same context than this previous blog post here. So as a reminder, we decided to benefit from the columnstore index feature after installing a new SQL Server 2012&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-12T18:00:58+00:00\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\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:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\"},\"headline\":\"Stored procedure execution may fail with nonclustered columnstore indexes\",\"datePublished\":\"2016-05-12T18:00:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/\"},\"wordCount\":369,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"keywords\":[\"Columnstore\",\"Data Warehouse\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/\",\"name\":\"Stored procedure execution may fail with nonclustered columnstore indexes\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-05-12T18:00:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Stored procedure execution may fail with nonclustered columnstore indexes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"caption\":\"Microsoft Team\"},\"description\":\"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Stored procedure execution may fail with nonclustered columnstore indexes","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.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","og_locale":"en_US","og_type":"article","og_title":"Stored procedure execution may fail with nonclustered columnstore indexes","og_description":"In this blog post, let\u2019s talk about a weird situation where I faced a nonclustered columnstore issue with SQL Server 2012. Let\u2019s say we are in the same context than this previous blog post here. So as a reminder, we decided to benefit from the columnstore index feature after installing a new SQL Server 2012&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2016-05-12T18:00:58+00:00","author":"Microsoft Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294"},"headline":"Stored procedure execution may fail with nonclustered columnstore indexes","datePublished":"2016-05-12T18:00:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/"},"wordCount":369,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"keywords":["Columnstore","Data Warehouse"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","url":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","name":"Stored procedure execution may fail with nonclustered columnstore indexes","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-05-12T18:00:58+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Stored procedure execution may fail with nonclustered columnstore indexes"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","caption":"Microsoft Team"},"description":"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7930","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7930"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7930\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7930"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7930"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7930"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}