Columnstore index on multi-tenant database
Is it still a good idea to use NCCI indexes for multitenants database?


If the alternative is to have a data structure that efficiently filters by tenant, then no.  

If TenantId is the leading column in your clustered index, then single-tenant queries will be pretty fast.  The NCCI row groups will not be segregated by tenant, and so all the row groups will need to be scanned to find rows for a single tenant.  

As rows are inserted, they are inserted into the "middle" of the clustered index, typically at the "end" of the rows for the tenant.  But rows are always inserted into the delta store for an CCI/NCCI.  And whenever the delta store has 1 million rows in it, it's rebuilt into a columnar row group.  So each row group will end up with the last million rows inserted across all tenants.

You can fix this with partitioning by TenantID, which could give each tenant (or group of tenants) a separate physical table (or NCCI).  Each partition will have its own delta store, which will fill up with single-tenant rows.  If you do partition, each partition might end up too small for a Columnstore to be useful. You'll want a few million rows per partition at least.  

In general with multi-tenant data, you want to avoid interleaving tenant data in a way that requires you to read all tenants data to retrieve a single tenant's data.