21

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??

Improve this question
8
  • As the feature matures, you will see more and more of these features become supported (heck, in 2012, columnstore indexes were read only!). In the meantime, you are offered a trade-off - great performance with limitations, or same old same old. I also don't believe they intended that to mean that every table in your DW should have clustered columnstore indexes and that no tables should have any constraints - there are probably a limited number of tables in any DW that would give you huge bang for the buck.
    – Aaron Bertrand
    Oct 8, 2014 at 15:18
  • 3
    Beware -it can handle joins. A FK relationship is totally not needed for a join. It is there to handle referential integrity - which is nice to have but in a data warehouse CAN be omitted. At a risk, yes, but also with a performance gain.
    – TomTom
    Oct 8, 2014 at 15:33
  • 8
    Also - "no real new value"? You mean being writable and clustered don't sound like improvements to you? Having users be able to query data in real time instead of waiting for a drop and rebuild to get more current data doesn't seem like a good thing for your users and less maintenance for you? shrug
    – Aaron Bertrand
    Oct 8, 2014 at 15:40
  • You can have (unique) indexes by creating an indexed view. It seems the infrastructure for index maintenance is already there. It's just that normal indexes are not (yet) implemented.
    – usr
    Oct 8, 2014 at 16:09
  • @AaronBertrand In a DWH scenario with fact tables with foreign key's the Clustered Columnstore index does not work. This in Large contrast with Microsoft expecting this as the standard to store large fact tables. I hope you can prove me wrong...? Because I like SQL Server.
    – OverflowStack
    Oct 9, 2014 at 10:55

3 Answers 3

Reset to default
17

You've got lots of questions in here:

Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....)

A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes don't support that yet.

Q: So MS advocates Clustered Column store indexes for DWH scenarios, However it can not handle FK relationships?!

A: Microsoft gives you tools. It's up to you how you use those tools.

If your biggest challenge is a lack of data integrity in your data warehouse, then the tool you want is conventional tables with foreign keys.

If your biggest challenge is query performance, and you're willing to check your own data integrity as part of the loading process, then the tool you want is clustered columnstore indexes.

Q: However SQL 2014 than adds no real new value for DWH??

A: Thankfully, clustered columnstore wasn't the only new feature in SQL Server 2014. For example, check out the new cardinality estimator.

Q: Why am I so angry and bitter about the way my favorite feature was implemented?

A: You caught me - you didn't really ask that question - but I'll answer it anyway. Welcome to the world of third party software where not everything is built according to your exact specifications. If you feel passionately about a change you'd like to see in a Microsoft product, check out Connect.Microsoft.com. It's their feedback process where you can submit a change, other people can vote it up, and then the product team reads it and tells you why they won't implement it. Sometimes. Most of the time they just mark it as "won't fix, works on my machine" but hey, sometimes you do get some answers.

Improve this answer
2
  • "Correct, it's normally a good practice to have foreign keys in a data warehouse" - > SQLCAT - Top 10 Best Practices for Building a Large Scale Relational Data Warehouse ... "Build nonclustered indexes for each foreign key." -> Nothing about enforcing the FK relationship mentioned in the link, and the non-CI is redundant due to columnstore, so would point to no need for FK on fact table, would you agree? Interested in your thoughts on this.
    – Adrian Torrie
    Feb 3, 2015 at 4:30
  • 1
    ... and for dimensions: "Avoid enforcing foreign key relationships between the fact and the dimension tables, to allow faster data loads. You can create foreign key constraints with NOCHECK to document the relationships; but don’t enforce them. Ensure data integrity though Transform Lookups, or perform the data integrity checks at the source of the data"
    – Adrian Torrie
    Feb 3, 2015 at 4:31
6

I can understand that you feel some pieces that you are used to are missing. But that is only because they are missing.

Nonetheless, SQL Server was being successfully used when Foreign Keys were just a concept (which we implemented through triggers in those days), not a physical implementation such as a constraint. Declarative Referential Integrity was there at least by SQL Server 7.0, but much weaker than the current implementation.

Regarding the value of the Clustered ColumnStore Index it does provide an index and the rows are updateable. You might find this discussion valuable: http://sqlwithmanoj.com/2014/07/24/maintaining-uniqueness-with-clustered-columnstore-index-sql-server-2014/

Manoj points out that there is a way to create an Indexed/Materialized View on top of this table, with Clustering Key as the PK (1st column of the table/view). Whether that suits you, of course, is a decision you have to make.

But, as Aaron Bertrand and TomTom commented, this is all about better performance. If you can manage the other issues that concern you (and I believe that they are manageable) then you get quite a few benefits. So use the ColumnStore for what is is able to do and manage the missing features yourself.

Improve this answer
2

This question pertains to SQL 2014, but I want to provide additional information in light of changes made in SQL 2016 to columnstore indexes, since it can be hard to sort out the limitations in different versions and this question still surfaces fairly high on Google:

For SQL 2016, Microsoft describes a method to use nonclustered btree indexes (which can now be added as secondary indexes on a clustered columnstore table) to enforce foreign key constraints, provided the constraint is added prior to the columnstore index: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance

Niko Neugebauer also has a blog post about this; it's actually possible to directly create unique/foreign constraints on columnstore tables (I've been applying this approach in my work): http://www.nikoport.com/2015/09/15/columnstore-indexes-part-66-more-clustered-columnstore-improvements-in-sql-server-2016/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.