Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows. And also useful with queries, which selects only the subset of available columns in such tables.
What happens if we break these two "rules"/best practices?
- Like having a clustered column store indexed table which will only store few thousand, or hundreds of thousands of rows max.
- And running queries against those clustered column store table where all the columns are needed.
My tests don't reveal any performance degradation comparing to row stored clustered index table. Which is great in our case.
Is there any "long term" effects breaking these two rules? Or any hidden pitfalls which haven't showed up just yet?
Context why is it needed: I designed a database model which will be used for many instances of different vendor databases. The schema remains the same in every database, but different vendors have different amount of data. Hence few small vendors may end up with small amount of data (<1 000 000) in their tables. I can't allow myself to keep up two different database for row-store and column-store model.