New features for SQL Server 2016: Columnstore indexes new features

New features for SQL Server 2016: Columnstore indexes new features

1. A row store table can have an updatable Columnstore index, and the previously nonclustered Columnstore index is read-only.
2. Nonclustered Columnstore indexes support filter criteria.
3. In a memory-optimized table, you can have a Columnstore index that can be created when you create a table, or on a subsequent ALTER TABLE statement. The Columnstore index was not supported by memory-optimized tables before.
4. A clustered Columnstore index can have multiple nonclustered row store indexes, and a nonclustered index is not supported by a Columnstore index.
5. Supports the inclusion of primary and foreign key constraints on the clustered Columnstore index, which constrains the use of the Btree index.
6. The column clustered index has a compression delay option that minimizes the impact of transaction comps.

Database performance with compatibility level 120,130:

1. The Columnstore index supports read-committed snapshot isolation levels and snapshot isolation levels. This allows the query to maintain transactional consistency without locking
2. The Columnstore index does not need to clean up deleted rows by rebuilding the index, which can be cleaned by the ALTER TABLE......REORGANIZE statement
3. Columnstore indexes can be accessed in AlwaysOn readable secondary replica.
4. To improve performance, SQL Server calculates the maximum, newest, total, count, and average aggregation of a column that is smaller than 8 bytes when the table is scanned for pushdown.
5. When comparing string types, the condition is pushdown to improve the query speed.

Database performance with compatibility level 130:

1. New Batch mode supports operations:
   • SORT
   • multiple aggregate functions
   • windows aggregate functions
   • Windows user-defined aggregations
   • Windows Aggregation analysis functions
2. single-threaded queries can run in batch mode
3. Memory-Optimized table queries can be parallel in interop mode when the data accessed is row or column storage

Support: These system views is new for columnstore:-sys.column_store_row_groups (Transact-SQL)-sys.dm_column_store_object_ POOL (Transact-SQL)-sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)-Sys.dm_db_column_store_row _GROUP_PHYSICAL_STATS (Transact-SQL)-sys.dm_db_index_operational_stats (Transact-SQL)-Sys.dm_db_index_physical_ STATS (Transact-SQL)-sys.internal_partitions (Transact-SQL) These in-memory oltp-based DMVs contain updates for columnst ore:-sys.dm_db_xtp_hash_index_stats (Transact-SQL)-sys.dm_db_xtp_index_stats (Transact-SQL)-Sys.dm_db_xtp_memory _CONSUMERS (Transact-SQL)-sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)-Sys.dm_db_xtp_object_stats ( Transact-SQL)-sys.dm_db_xtp_table_memory_stats (Transact-SQL) Restrictions:

1. Merge is disabled when the Btree index is defined in the clustered Columnstore index.
2. For memory-optimized tables, the Columnstore index must contain all columns and cannot use a filter.
3. For memory-optimized tables, the Columnstore index runs only in interop mode and supports concurrent queries.

New features for SQL Server 2016: Columnstore indexes new features