{"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;]<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368],"tags":[774,526,837,51],"type_dbi":[],"class_list":["post-7930","post","type-post","status-publish","format-standard","hentry","category-development-performance","tag-columnstore","tag-datawarehouse","tag-fact-tables","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - 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;]\" \/>\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 Blog\" \/>\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: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\/bfab48333280d616e1170e7369df90a4\"},\"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,\"keywords\":[\"columnstore\",\"DataWareHouse\",\"fact tables\",\"SQL Server\"],\"articleSection\":[\"Development &amp; Performance\"],\"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\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"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 Blog\",\"description\":\"\",\"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\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"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;]","og_url":"https:\/\/www.dbi-services.com\/blog\/stored-procedure-execution-may-fail-with-nonclustered-columnstore-indexes\/","og_site_name":"dbi Blog","article_published_time":"2016-05-12T18:00:58+00:00","author":"Microsoft Team","twitter_card":"summary_large_image","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\/bfab48333280d616e1170e7369df90a4"},"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,"keywords":["columnstore","DataWareHouse","fact tables","SQL Server"],"articleSection":["Development &amp; Performance"],"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","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"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 Blog","description":"","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":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7930"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}