{"id":2203,"date":"2013-07-17T09:00:00","date_gmt":"2013-07-17T16:00:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/"},"modified":"2024-01-22T22:49:13","modified_gmt":"2024-01-23T06:49:13","slug":"whats-new-for-columnstore-indexes-in-sql-server-2014","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/","title":{"rendered":"What\u2019s New for Columnstore Indexes in SQL Server 2014"},"content":{"rendered":"

In SQL Server 2012, we introduced the memory optimized columnstore index. This resulted in huge query performance improvements but columnstore indexes had one significant disadvantage \u2013 they couldn\u2019t be updated directly but had to be rebuilt on a base table. In SQL Server 2012, anyone wanting to take advantage of a this new index and also wanting to update the data behind it had to either drop the index and update the data, do some partition switching or unioning queries to the columnstore index and a regular row store containing the changing data. These workarounds are no longer required in <\/span>SQL Server 2014<\/span><\/a> as we have enhanced the columnstore to be a pure columnar store, so indexing is no longer required..<\/span><\/p>\n

In this next version, we\u2019re introducing enhancements to the In-Memory ColumnStore for data warehousing<\/i> implemented as a clustered columnstore index (or CCI) on a table. The data in a CCI is grouped and stored for every column in the table. Unlike the columnstore index, the CCI is the data \u2013 there is no other underlying data structure.<\/span><\/p>\n

(Short aside – this might be a little confusing especially to those that haven\u2019t lived and breathed SQL Server for the last little while. SQL has had two traditional table structures – heaps and clustered tables. Heaps, like the name suggests, aren\u2019t ordered by anything. Clustered tables are ordered by the clustered index \u2013 in fact, the clustered index is<\/i> the table. So CCI is analogous to a clustered index in that it represents the table as well.)<\/span><\/p>\n

Unlike a columnstore index, CCIs are updateable just like any other table. (The internal details of what happens under the covers when updating a CCI are interesting and we\u2019ll get to that in a separate post.) Now that the DML problem associated with columnstore indexes have gone away with CCIs, we can now make apples to apples comparison between columnstore and row store.<\/span><\/p>\n

Organizing the table by column provides substantial advantages. The first one is improved query performance. When data is organized and compressed by individual columns, scan operations are significantly faster. We typically expect data warehousing type queries (ie, queries with aggregates involving large scans) to be about 10x faster (no kidding!). I\u2019ve seen some almost unbelievable performance numbers much better than that. So if you\u2019re developing or managing a data warehousing application, you owe yourself a favor to check this out solely for the query performance.<\/span><\/p>\n

Second thing to expect with CCI is about a 2x improvement in data compression compared to row store page compression. Mileage may vary based on variability of values in each column, but results in this regard have been pretty consistent in my experience.<\/span><\/p>\n

There\u2019s also a new compression format \u201cCOLUMNSTORE_ARCHIVE\u201d that\u2019s available for columnstore indexes only. With the new archival compression, we can further compress the data which results in even less disk space being used. Internal tests have shown compression rates roughly double with the combination of a columnstore index and archival compression.<\/span><\/p>\n

In our next blog entries, we\u2019ll go into more details and show examples on how the new in-memory columnstore functionality works. <\/span><\/p>\n

SQL Server 2014 CTP1 is now available for download <\/span>here<\/span><\/a>.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

In SQL Server 2012, we introduced the memory optimized columnstore index. This resulted in huge query performance improvements but columnstore indexes had one significant disadvantage \u2013 they couldn\u2019t be updated directly but had to be rebuilt on a base table.<\/p>\n","protected":false},"author":1457,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"post_tag":[],"product":[],"content-type":[2448],"topic":[],"coauthors":[2487],"class_list":["post-2203","post","type-post","status-publish","format-standard","hentry","content-type-updates"],"yoast_head":"\nWhat\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog<\/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.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog\" \/>\n<meta property=\"og:description\" content=\"In SQL Server 2012, we introduced the memory optimized columnstore index. This resulted in huge query performance improvements but columnstore indexes had one significant disadvantage \u2013 they couldn\u2019t be updated directly but had to be rebuilt on a base table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft SQL Server Blog\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/sqlserver\" \/>\n<meta property=\"article:published_time\" content=\"2013-07-17T16:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-23T06:49:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"SQL Server Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:site\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"SQL Server Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/\",\"@type\":\"Person\",\"@name\":\"SQL Server Team\"}],\"headline\":\"What\u2019s New for Columnstore Indexes in SQL Server 2014\",\"datePublished\":\"2013-07-17T16:00:00+00:00\",\"dateModified\":\"2024-01-23T06:49:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\"},\"wordCount\":542,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\",\"name\":\"What\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\"},\"datePublished\":\"2013-07-17T16:00:00+00:00\",\"dateModified\":\"2024-01-23T06:49:13+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What\u2019s New for Columnstore Indexes in SQL Server 2014\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"name\":\"Microsoft SQL Server Blog\",\"description\":\"Official News from Microsoft\u2019s Information Platform\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\",\"name\":\"Microsoft SQL Server Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"width\":259,\"height\":194,\"caption\":\"Microsoft SQL Server Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/www.facebook.com\/sqlserver\",\"https:\/\/x.com\/SQLServer\",\"https:\/\/www.youtube.com\/user\/MSCloudOS\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog","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.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/","og_locale":"en_US","og_type":"article","og_title":"What\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog","og_description":"In SQL Server 2012, we introduced the memory optimized columnstore index. This resulted in huge query performance improvements but columnstore indexes had one significant disadvantage \u2013 they couldn\u2019t be updated directly but had to be rebuilt on a base table.","og_url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/","og_site_name":"Microsoft SQL Server Blog","article_publisher":"http:\/\/www.facebook.com\/sqlserver","article_published_time":"2013-07-17T16:00:00+00:00","article_modified_time":"2024-01-23T06:49:13+00:00","og_image":[{"width":512,"height":512,"url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png","type":"image\/png"}],"author":"SQL Server Team","twitter_card":"summary_large_image","twitter_creator":"@SQLServer","twitter_site":"@SQLServer","twitter_misc":{"Written by":"SQL Server Team","Est. reading time":"2 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/","@type":"Person","@name":"SQL Server Team"}],"headline":"What\u2019s New for Columnstore Indexes in SQL Server 2014","datePublished":"2013-07-17T16:00:00+00:00","dateModified":"2024-01-23T06:49:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/"},"wordCount":542,"commentCount":7,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/","name":"What\u2019s New for Columnstore Indexes in SQL Server 2014 - Microsoft SQL Server Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website"},"datePublished":"2013-07-17T16:00:00+00:00","dateModified":"2024-01-23T06:49:13+00:00","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/07\/17\/whats-new-for-columnstore-indexes-in-sql-server-2014\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/"},{"@type":"ListItem","position":2,"name":"What\u2019s New for Columnstore Indexes in SQL Server 2014"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","name":"Microsoft SQL Server Blog","description":"Official News from Microsoft\u2019s Information Platform","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization","name":"Microsoft SQL Server Blog","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","width":259,"height":194,"caption":"Microsoft SQL Server Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/www.facebook.com\/sqlserver","https:\/\/x.com\/SQLServer","https:\/\/www.youtube.com\/user\/MSCloudOS"]}]}},"msxcm_display_generated_audio":false,"msxcm_animated_featured_image":null,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/2203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/users\/1457"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/comments?post=2203"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/2203\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/media?parent=2203"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/post_tag?post=2203"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/product?post=2203"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/content-type?post=2203"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/topic?post=2203"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/coauthors?post=2203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}