Issue link: http://hub-nl.insight.com/i/692679
10 C H A P T E R 1 | Faster queries Note You can create a nonclustered columnstore index for disk-based tables only. SQL Server 2016 does not support this type of index for memory-optimized tables. If your OLTP workload performs many updates and deletes, the nonclustered columnstore index might become significantly fragmented and consequently less efficient. In this situation, you should consider using the COMPRESSION_DELAY option as described at "Real-time Operational Analytics: Compression Delay Option for Nonclustered Columnstore Index (NCCI)," https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational- analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/. Figure 1-3: Indexing for hot and warm data in a disk-based table to support analytic queries. If your OLTP table is an in-memory table, then columnstore technology benefits both the OLTP and analytic workloads. Use the INDEX…CLUSTERED COLUMNSTORE argument when you create the table, as shown in Example 1-8. When you use this argument, all columns in the table are stored as a clustered columnstore index. Example 1-8: Creating an in-memory table with a columnstore index CREATE TABLE UserSession ( SessionID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, UserID INT NOT NULL, CreatedDate DATETIME2(7) NOT NULL, ShoppingCartId INT, INDEX ix_usersession_cci CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON); Note Even when implementing a columnstore index, your analytics queries might require significant server resources such as CPU, I/O, or memory that adversely impact your OLTP workloads. In this case, another option to consider is using an AlwaysOn configuration to offload the analytics workload to a readable secondary.