{"id":2365,"date":"2016-07-01T17:13:31","date_gmt":"2016-07-01T16:13:31","guid":{"rendered":"http:\/\/andreas-wolter.com\/die-sql-server-2016-in-memory-evolution\/"},"modified":"2017-10-18T10:44:27","modified_gmt":"2017-10-18T09:44:27","slug":"the-sql-server-2016-in-memory-evolution","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/the-sql-server-2016-in-memory-evolution\/","title":{"rendered":"The SQL Server 2016 In-Memory Evolution \u2013 from row-store to columnstore to in-memory operational analytics"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-777f0c88aad043d30b9e261cbd0a8c23\">\n#top .av-special-heading.av-av_heading-777f0c88aad043d30b9e261cbd0a8c23{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-777f0c88aad043d30b9e261cbd0a8c23 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-777f0c88aad043d30b9e261cbd0a8c23 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-777f0c88aad043d30b9e261cbd0a8c23 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_hr  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >The SQL Server 2016 In-Memory Evolution<\/h3><div class='av-subheading av-subheading_below'><p>\u2013 from row-store to columnstore to in-memory operational analytics<\/p>\n<\/div><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-1  el_after_av_heading  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>SQL Server 2016 is finally available and, by extension, the \u201cversion 2\u201d of the In-Memory OLTP Engine, if you will.<\/p>\n<ul>\n<li>At various international conferences and <a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2014\/05\/artikel-sql-server-2014-richtigstellungen\" target=\"_blank\" rel=\"noopener\">already at the IX in 2014<\/a> I have presented what is behind the In-Memory Engine of SQL Server introduced in SQL Server 2014. Only I had not yet found the time to put it into a blog article so far.<\/li>\n<\/ul>\n<p>In this article I will illuminate the innovations and improvements Microsoft has been working on for the past 2 years, and which can be attributed much to customer feedback. <!--more-->In fact, feedback that to a great extent consisted of notes like \u201cnot practicable because this and this is missing.\u201d<\/p>\n<p>And let me say one thing before I start: in my view, Microsoft has been able to address the majority of blockers.<\/p>\n<p>That means, <strong>everybody should at least consider evaluating<\/strong> <strong>In-Memory<\/strong>, and <strong>in almost all database projects there are structures that can be solved more elegantly In-Memory<\/strong>. \u2013 Ok, maybe not for everybody, because this feature is unfortunately limited to the enterprise edition.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>The most important innovations for memory-optimized tables<\/strong> are:<\/p>\n<p>It is now possible to define Unique Indexes as well as foreign key constraints. The latter are only possible between memory-optimized tables (and not between disk-\/page-based and memory-optimized tables), and must always refer to the primary key \u2013 referring to Unique Indexes is not possible.<\/p>\n<p>Moreover, NULL-values in Non-Unique Indexes are now allowed (as opposed to disk-based tables not in Unique Indexes!).<\/p>\n<p>Equally very important is the support of all code pages and of non-Unicode data as well as the encryption of memory-optimized data with TDE (hence not in the main memory itself but of the data that stored on disk). *1<\/p>\n<p><strong>In my view, these <u>were<\/u> the most frequent blockers in projects in which In-Memory was evaluated<\/strong>, as there were hardly any practicable workarounds for this issue.<\/p>\n<p>*1 Data encryption with the ENCRYPTION functions in SQL Server is not supported \u2013 this is also true for the new Always Encrypted Technology and Dynamic Data Masking.<\/p>\n<p>Row-Level Security of SQL Server 2016 yet is supported. The predicates and functions must consequently be compiled natively.<\/p>\n<p>Very cool, if you ask me.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>A further limitation has been eliminated with the <strong>possibility of altering Memory-optimized tables afterwards<\/strong>.<\/p>\n<p>Adding, dropping and altering columns and indexes afterwards is supported. Instead of CREATE\/ALTER\/DROP index it must now be used ALTER TABLE, since in Memory-optimized tables indexes are part of the table definition (and are being compiled in its entirety).<\/p>\n<p>It is particularly important here that it is now also possible to change the bucket count of Hash-indexes which during operation may naturally change considerably over time.<\/p>\n<p>This is how it looks in a sample code:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-84a04255ac70c44344f8dc9e1a8753e6\">\n.avia-image-container.av-av_image-84a04255ac70c44344f8dc9e1a8753e6 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-84a04255ac70c44344f8dc9e1a8753e6 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-84a04255ac70c44344f8dc9e1a8753e6 av-styling- avia-align-center  avia-builder-el-5  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2343 avia-img-lazy-loading-not-2343 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Add_Alter_Drop_Hash_Index.png\" alt='' title='1606_Add_Alter_Drop_Hash_Index'  height=\"370\" width=\"606\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Add_Alter_Drop_Hash_Index.png 606w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Add_Alter_Drop_Hash_Index-600x366.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Add_Alter_Drop_Hash_Index-300x183.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Add_Alter_Drop_Hash_Index-450x275.png 450w\" sizes=\"(max-width: 606px) 100vw, 606px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Statistics can also be updated with SAMPLE instead of FULLSCAN, and, above all, automatically as well.<\/p>\n<p>Data type: LOB datatypes such as varchar\/varbinary(max) are supported and stored \u201coff-row.\u201d<\/p>\n<p><strong>Important, newly supported T-SQL functionalities<\/strong> within <strong>Natively compiled Stored Procedures and, brand new, Functions<\/strong>, are: the OUTPUT clause, UNION and UNION ALL, DISTINCT, OUTER JOINs, subqueries.<\/p>\n<p>Moreover, natively compiled procedures can now also be changed with ALTER PROCEDURE. In this way, they will naturally be stored compiled in the new shape in the last step.<\/p>\n<p>In order to facilitate a new implementation plan in the case of changed statistics one can now also executive sp_recompile against natively compiled procedures (and functions).<\/p>\n<p><strong>Performance<\/strong>, too, was further tweaked. As a result, memory-optimized tables and Hash-indexes can now (in InterOP mode) be scanned simultaneously. In the IO area, the entire checkpoint process was reviewed and the data files can now be read and written with multiple threads, which may result in an almost tenfold increase of the throughput (if the IO-subsystem keeps up with it).<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-112da87c4df0e68390d5f03d8551cae1\">\n.avia-image-container.av-av_image-112da87c4df0e68390d5f03d8551cae1 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-112da87c4df0e68390d5f03d8551cae1 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-112da87c4df0e68390d5f03d8551cae1 av-styling- avia-align-center  avia-builder-el-7  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2345 avia-img-lazy-loading-not-2345 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP.png\" alt='' title='1606_Parallel_Scan_HashIndex_In-MemoryOLTP'  height=\"196\" width=\"1215\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP.png 1215w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-600x97.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-300x48.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-768x124.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-1030x166.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-705x114.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-1210x196.png 1210w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Parallel_Scan_HashIndex_In-MemoryOLTP-450x73.png 450w\" sizes=\"(max-width: 1215px) 100vw, 1215px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Columnstore Technology<\/strong><\/p>\n<p>What has been going on in the other Storage-Engine \u201cVertipaq\u201d, integrated in SQL Server since 2012, with the Columnstored Indexes? These are also Main-memory optimized, but with an entirely different objective:<\/p>\n<p>Storage space optimization and efficient OLAP-style queries.<\/p>\n<p>The innovations here are very essential:<\/p>\n<p><strong>Both Columnstore Index Types, Clustered and Nonclustered, can now be updated!<\/strong><\/p>\n<p>Additionally, <strong>Columnstore Indexes can now be extended with further traditional btree-indexes.<\/strong> This is important, as not every query really profits from the Columnstore storage form. This gain in flexibility is a decisive advantage over the previous releases and cannot be emphasized enough.<\/p>\n<p>And something else is now possible: Nonclustered Columnstore can be created with a filter.<\/p>\n<p><strong>By means of new techniques the following problem can be solved, for example: <\/strong><\/p>\n<p>A table with sales transactions is filled by small inserts at intervals of seconds.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>At the same time, one would also like to provide various reports on day and daytime aggregations. Maximally up to date of course.<\/p>\n<p>The problem typically lies in the fact that one has to decide between indexes for all report queries and those that are minimally required for possible updates. Inserts viewed in isolation do not require any indexes.<\/p>\n<p>This combination results in the OLTP tables overloaded with many indexes, which I frequently discover during my work and that then need to be \u201coptimized\u201d (removed).<\/p>\n<p>The possibility to create a Nonclustered Columnstore Index in addition to the Clustered Index does not only save Indexes (because the Columnstore Index can cover every necessary column), but with a smartly applied filter the Index-Overhead can also be avoided that would otherwise affect the actually more important inserts.<\/p>\n<p><strong>The mixing of OLTP and OLAP queries are one of the most typical problems in databases<\/strong>, and these new possibilities are thus simply a dream for database architects.<\/p>\n<p>This is how it will look in code:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-fc7f41c91d777c8471fb8fffd23f7d84\">\n.avia-image-container.av-av_image-fc7f41c91d777c8471fb8fffd23f7d84 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-fc7f41c91d777c8471fb8fffd23f7d84 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-fc7f41c91d777c8471fb8fffd23f7d84 av-styling- avia-align-center  avia-builder-el-10  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2347 avia-img-lazy-loading-not-2347 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Filtered_NonclusteredColumnstore_Index_SQLCode.png\" alt='' title='1606_Filtered_NonclusteredColumnstore_Index_SQLCode'  height=\"239\" width=\"698\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Filtered_NonclusteredColumnstore_Index_SQLCode.png 698w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Filtered_NonclusteredColumnstore_Index_SQLCode-600x205.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Filtered_NonclusteredColumnstore_Index_SQLCode-300x103.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_Filtered_NonclusteredColumnstore_Index_SQLCode-450x154.png 450w\" sizes=\"(max-width: 698px) 100vw, 698px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>40% Performance-Improvement in TPC-H Benchmark<\/strong><\/p>\n<p>In terms of performance, these improvements have made the SQL Server 2016 pull ahead of SQL Server 2014 by close to 40% more QphH (Query-per-Hour Performance Metric) in the <a href=\"http:\/\/www.tpc.org\/tpch\/results\/tpch_perf_results.asp?resulttype=noncluster%C2%A0\" target=\"_blank\" rel=\"noopener\">TPC-H Benchmark<\/a>. You can see in the screenshot that the Benchmark was sent in on 9 March 2016 and really was achieved on the same hardware as under SQL Server 2014 on 1 May 2015.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-3f3a22ce635ac83f83286dc252502742\">\n.avia-image-container.av-av_image-3f3a22ce635ac83f83286dc252502742 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-3f3a22ce635ac83f83286dc252502742 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-3f3a22ce635ac83f83286dc252502742 av-styling- avia-align-center  avia-builder-el-12  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2349 avia-img-lazy-loading-not-2349 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB.png\" alt='' title='1606_TPC-H_3000GB'  height=\"494\" width=\"1166\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB.png 1166w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-600x254.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-300x127.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-768x325.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-1030x436.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-705x299.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_TPC-H_3000GB-450x191.png 450w\" sizes=\"(max-width: 1166px) 100vw, 1166px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Further important improvements<\/strong> <strong>for Columnstore<\/strong> include the support of the SNAPSHOT Isolation Level (and RCSI), which is especially important to Read-Only Replicas of Availability Groups, as well as online-defragmentation and various analysis enhancements.<\/p>\n<p><strong>The highlight: Real-time Operational Analytics &#038; In-Memory OLTP<\/strong><\/p>\n<p>The absolute highlight however is surely the combination of memory-optimized tables and ColumnStore Indexes.<\/p>\n<p>As a result, two features that are <u>optimized<\/u> for entirely opposing query types \u2013 OLTP and OLAP \u2013 are merged.<\/p>\n<p><strong>Technical facts<\/strong><\/p>\n<p>In technical terms, a Clustered Columnstore Index is applied. As can be seen in the image, it omits the \u201chot-spot\u201d of the data in order to prevent the overhead through the double data storage in case of alterations and the potentially quick succession of inserts in this area. In addition to the implied Delta Rowgroup (in the image: Tail) that is covered by the memory-optimized index, there is a \u201cdeleted rows table\u201d for deleted data. Both areas are asynchronously compressed\/added to the CCI according to the Columnstore Index standard threshold value of 1 million cells.<\/p>\n<p>At this point, let me add another note: the <strong>maximum data amount<\/strong> that can be stored per database in (durable) memory-optimized tables has now been eliminated, too!<\/p>\n<p>As a result, according to the current technical state, in theory <strong>up to 12 TB (less a maintenance overhead) can be stored in XTP-memory under Windows Server 2016!<\/strong><\/p>\n<p>In terms of structure, it would look like this:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-af74cbe83da0779d41244ac2663552b6\">\n.avia-image-container.av-av_image-af74cbe83da0779d41244ac2663552b6 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-af74cbe83da0779d41244ac2663552b6 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-af74cbe83da0779d41244ac2663552b6 av-styling- avia-align-center  avia-builder-el-14  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2351 avia-img-lazy-loading-not-2351 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_SQL2016_Real_time_operational_analytics.png\" alt='' title='1606_SQL2016_Real_time_operational_analytics'  height=\"334\" width=\"419\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_SQL2016_Real_time_operational_analytics.png 419w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_SQL2016_Real_time_operational_analytics-300x239.png 300w\" sizes=\"(max-width: 419px) 100vw, 419px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>And applied in code, it would look like this:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-edf07875e1c55a97ac65201c090f0aa6\">\n.avia-image-container.av-av_image-edf07875e1c55a97ac65201c090f0aa6 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-edf07875e1c55a97ac65201c090f0aa6 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-edf07875e1c55a97ac65201c090f0aa6 av-styling- avia-align-center  avia-builder-el-16  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2353 avia-img-lazy-loading-not-2353 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode.png\" alt='' title='1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode'  height=\"591\" width=\"811\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode.png 811w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode-600x437.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode-300x219.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode-768x560.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode-705x514.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode-450x328.png 450w\" sizes=\"(max-width: 811px) 100vw, 811px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>The outcome now offers <strong>the best from both worlds:<\/strong> high performing inserts\/updates\/deletes and singleton-queries, and at the same time high performing analytic queries that handle many millions of cells at once \u2013 and in fact at the same time in the same table!<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-ad46b3986f3ecbd41e96f1ae01bd9929\">\n.avia-image-container.av-av_image-ad46b3986f3ecbd41e96f1ae01bd9929 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-ad46b3986f3ecbd41e96f1ae01bd9929 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-ad46b3986f3ecbd41e96f1ae01bd9929 av-styling- avia-align-center  avia-builder-el-18  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2355 avia-img-lazy-loading-not-2355 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index.png\" alt='' title='1606_MemoryOptimizedTable_ClusteredColumnstore_Index'  height=\"195\" width=\"390\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index.png 390w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1606_MemoryOptimizedTable_ClusteredColumnstore_Index-300x150.png 300w\" sizes=\"(max-width: 390px) 100vw, 390px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>One <strong>restriction in querying<\/strong> is that the Columnstore Index in memory-optimized tables only works in the InterOP mode \u2013 thus not in natively compiled procedures.<\/p>\n<p>Which leads us to the last Topic:<\/p>\n<p><strong>Open points, missing feature support<\/strong><\/p>\n<p>There are of course still a number of features that have been taken over into SQL Server due to the decade-long development of the SQL language, but which have not made it into the new XTP Engine yet. This is not just because the latter is \u201csimply new\u201d but also because due to the completely different architecture of this engine, which is radically tailored to In-Memory, there are several significant differences vis-\u00e0-vis the traditional database engines.<\/p>\n<p><strong>Personally, I miss the following features the most:<\/strong><\/p>\n<ol>\n<li>DBCC CHECKDB\/CHECKTABLE for memory-optimized tables<\/li>\n<li>CASE-Statement<\/li>\n<li>Filtered Indexes<\/li>\n<li>CTEs<\/li>\n<li>Replication<\/li>\n<li>OFFSET-Operator<\/li>\n<li>Ranking Functions<\/li>\n<li>DDL Trigger for CREATE\/DROP TABLE and CREATE\/DROP PROCEDURE<\/li>\n<li>TRUNCATE TABLE<\/li>\n<li>DATA_COMPRESSION<\/li>\n<li>Data type datetimeoffset<\/li>\n<\/ol>\n<p>For the complete list, visit:<\/p>\n<p><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn246937.aspx\" target=\"_blank\" rel=\"noopener\">Transact-SQL Constructs Not Supported by In-Memory OLTP<\/a><\/p>\n<p><strong>Call to Action <\/strong><\/p>\n<p>Even though the list of missing feature\/function support is still quite long, only few really make full use of these features. And for most of the remaining \u201cblockers\u201d there are actually quite good workarounds, be it in the form of a different architecture or in code terms. One has to bear in mind that the In-Memory tables do not necessarily make sense for all scenarios, but rather for the top-affected tables. And as for the latter, one should already have put some effort into the design anyway.<\/p>\n<p>In general, I firmly believe that <strong>in almost every database project<\/strong> there are some instances <strong>that may profit from In-Memory functions<\/strong>.<\/p>\n<p>Why can I be so sure?<\/p>\n<p>Already since SQL 2014 it has been possible to use memory-optimized table variables aside from memory-optimized tables. And using these, in turn, many temptable-constructs can be replaced. Now that does not necessarily result in higher performing applications right away, but it is a good way to start dealing with In-Memory in terms of code and to slowly but surely start programming with it. A further \u201cQuick-Win\u201d can often be found in data warehouse architectures in the so-called \u201cstaging area,\u201d as it is frequently being applied in traditional DW-systems at the moment.<\/p>\n<p>And it is via these \u201cgateways\u201d that you have will have ended up in the \u201cIn-Memory world\u201d before you know it.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Cu In-Memory<\/p>\n<p>Andreas<\/p>\n<p>PS:<\/p>\n<p>If you are in India in August and want to advance your skills in those new technologies there is still a chance to get a seat in the Precon &#8220;Present and Future: In-Memory in SQL Server \u2013 from 0 to Operational Analytics Master&#8221; at SQL Server Geeks Summit in Bangalore on August 10.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-2356\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster.jpg\" alt=\"\" width=\"851\" height=\"315\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster.jpg 851w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster-600x222.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster-300x111.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster-768x284.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster-705x261.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1608_Pre-Con_Poster-450x167.jpg 450w\" sizes=\"auto, (max-width: 851px) 100vw, 851px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-21  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-22  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-24  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/the-sql-server-2016-in-memory-evolution\/&#038;t=The%20SQL%20Server%202016%20In-Memory%20Evolution%20%E2%80%93%20from%20row-store%20to%20columnstore%20to%20in-memory%20operational%20analytics\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=The%20SQL%20Server%202016%20In-Memory%20Evolution%20%E2%80%93%20from%20row-store%20to%20columnstore%20to%20in-memory%20operational%20analytics&#038;url=https:\/\/andreas-wolter.com\/en\/?p=2365\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=The%20SQL%20Server%202016%20In-Memory%20Evolution%20%E2%80%93%20from%20row-store%20to%20columnstore%20to%20in-memory%20operational%20analytics&#038;url=https:\/\/andreas-wolter.com\/en\/the-sql-server-2016-in-memory-evolution\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-25  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"SQL Server 2016 is finally available and, by extension, the \u201cversion 2\u201d of the In-Memory OLTP Engine, if you will. At various international conferences and already at the IX in 2014 I have presented what is behind the In-Memory Engine of SQL Server introduced in SQL Server 2014. Only I had not yet found the [&hellip;]","protected":false},"author":4,"featured_media":2924,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73,97,46,118,45,44],"tags":[23,85,25,86,87,96,202,88],"class_list":["post-2365","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-columnstore-index","category-conferences-en","category-in-memory-oltpxtp-htap","category-indexing-en","category-sql-server-2016","category-storage-engine","tag-columnstore-en","tag-htap-en","tag-in-memory-en","tag-in-memory-oltp-en","tag-indexing-en","tag-sql-server-2016-en","tag-sql-server-2016-en-2","tag-xtp-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2365","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=2365"}],"version-history":[{"count":6,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2365\/revisions"}],"predecessor-version":[{"id":2761,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2365\/revisions\/2761"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/2924"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=2365"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=2365"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=2365"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}