I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following:
"We view the clustered columnstore index as the standard for storing large data warehousing fact tables, and expect it will be used in most data warehousing scenarios. Since the clustered columnstore index is updateable, your workload can perform a large number of insert, update, and delete operations." http://msdn.microsoft.com/en-us/library/gg492088.aspx
However if you read further in the documentation you will find under limitations and restrictions:
"Cannot have unique constraints, primary key constraints, or foreign key constraints."
This confuses me a lot! It is a good practice (not mandatory) to have foreign keys in the data warehouse for a variety of reasons (data integrity, relations visible for semantic layer ...)
So Microsoft advocates clustered columnstore indexes for data warehouse scenarios; however, it can not handle foreign key relationships?!
Am I correct on this? Which other approaches would you advise? In the past, I have used a nonclustered columnstore index in data warehouse scenarios, with drop and rebuild for data loads. However SQL Server 2014 then adds no real new value for data warehouses??