Nonclustered Columnstore Indexes and SQL Server

A nonclustered columnstore index (NCCI) is always associated with a table, and there can only be one defined per table.

Any field that exists in the table, except BLOB data types, can be added to the NCCI.

Implementing NCCI

In contrast to SIFT keys, which rely on the indexed views feature in SQL Server, NCCIs in Business Central use only the nonclustered columnstore indexes feature. No aggregated data is stored in the NCCI—all analytical queries are done at runtime. This implementation also means you don't have to worry about the order of fields added to the NCCI or design specific NNCIs for specific flow field scenarios.

With SIFT keys, any insert, update, or delete operations to the underlying table will introduce some database locking because the indexed views must be updated as well. The more SIFT keys that are defined on the table, the more index maintenance is needed. This problem doesn't exist with a nonclustered columnstore index.

See Also

NCCI Overview)
NCCI Tuning and Tracing
NCCI Performance
Migrating from SIFT to NCCI