This might some as a big relief for those who use ODS for Reporting. Previously we have the following limitations with the below types of indexes
Clustered Index – good for fetching atomic rows within OLTP
Non Clustered Index – Good for fetching a set of rows and columns within OLTP
Non Clustered Columnstore Index NCCS – Good for Data warehousing queries but
not so great for OLTP workloads because it was severely limited by the fact that it prevented DML operations. But it did support the creation of traditional indexes so it gave the best of OLTP seek like behavior and DW batch like behavior.
Clustered Columnstore Index CCS – Good for DW operations but didn’t make the table read only and therefore was able to support OLTP workloads as well. However it was the only index allowed on the table and therefore queries that could have benefited from a traditional index were affected.
Now we have Updateable Non Clustered Columnstore indexes which means you can create a Clustered, Non Clustered and a NonClustered Columnstore index all on the same table and be able to write OLTP and DW queries on the same table, each using the index it sees fit.
So the only question I guess is which is better an “Updatable NonClustered Index” or a “Clustered Columnstore index”
What about space usage and IO?
When it comes to the space used NC_CS index naturally is at a disadvantage as shown on the below table. To find out I loaded A 3.4 million rows of Airline data to represent a typical Fact table of 30 + Columns.
What about performance?
When it comes to seeks traditional indexes are much better than Columnstore indexes and this is where the NC Columnstore index wins. Look at the below screenshot, you will see that both the Columnstore indexes perform poorly when it comes to seeks. The advantage the NCCS has over the CCS is that it allows traditional indexes to co-exist with the Columnstore index. So the DBA can now smartly create traditional indexes for OLTP workloads and Columnstore indexes for the DW workload and have everything run off the same tables without having to compromise on DML operations.
PS:- Clustered Columnstore index does support Non Clustered Traditional indexes but not Traditional clustered indexes since there can be only one clustered index columnstore or otherwise.