In this Essential Guide to SQL Server 2014 series I’ve been covering the most important new features and enhancements that Microsoft added to SQL Server 2014. In the last column I covered the SQL Server 2014’s Backup Enhancements. In this column I’m going to cover the important enhancements to the columnstore Index. Microsoft introduced the columnstore index in SQL Server 2012 and it provided significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries columnstore indexes can provide up to 10x performance improvements. Columnstore indexes are different from standard indexes. Instead of being organized by rows where data is stored in B-Tree structures like a standard index the columnstore index stores data in columnar fashion and uses extreme compression to reduce the disk I/O needed to retrieve data.
The original implementation of columnstore indexes provided good performance improvements for data warehousing style queries. However, it also had limitations. The underlying table had to be read-only. In order to update the table you needed to drop the columnstore index and then later recreate it once the updates to the base table were completed. SQL Server 2014 eliminated this restriction and the new columnstore index are updateable. Updates can be performed to the underlying table without first needing to drop the columnstore index. Let’s dig into SQL Server 2014’s enhancements to the columnstore index a little deeper.
SQL Server 2014 columnstore indexes are supported on the Enterprise, Developer, and Evaluation editions and cannot be combined with other indexes. Columnstore indexes are highly compressed. Data compression enables tables to be reduced in size up op 7x. SQL Server 2014 changes the way that the columnstore index is implemented in that it now supports both clustered and non-clustered columnstore indexes. SQL Server 2012 only supported non-clustered columnstore indexes. SQL Server 2014’s support for the clustered columnstore index is what enables the columnstore index to be updateable.
Characteristics of non-clustered columnstore indexes include:
-
It is not updateable
-
You can index a subset of columns
-
It requires extra storage to store a copy of the columns in the index
-
Sorting the data before creating
Characteristics of clustered columnstore indexes include:
-
Can be updated
-
All columns are included
-
Is the primary storage method for the entire table
You may wonder how the clustered columnstore index is able to support insert, update and delete operations when the data is so compressed. In order to process inserts, updates and deletes SQL Server 2014 uses deltastores and delete bitmaps – constructs that temporarily store changes. A background process asynchronously pushed the changes to the base table. SQL 2014 clustered columnstore indexes are supported on SQL Server AlwaysOn Availability Groups. However, there is one notable limitation -- because they don’t support the Snapshot Isolation level you cannot use clustered columnstore indexes on readable secondary replicas. You can learn more about SQL Server 2014’s enhanced columnstore Index on MSDN at Columnstore Indexes Described.
