Dynamics 365 Business Central : Migrating from SIFT to Nonclustered Columnstore Indexes.
In this article we will quickly cover the highlights of Business central migrating from SIFT to Nonclustered columnstore indexes (NCCI). Very precise blog more detailed available on MS documentation.
As we all know, from the Navision days that SIFT (SumIndexField) is used to calculate the sum of numeric data type (like decimal, integer e.t.c) columns in the table. SIFT is used to optimize the performance and CalcSums calculates the sum of one or more fields that are SumIndexFields in the record. When a record is inserted, updated or deleted from a table, the SIFT keys for the tables are maintained. Maintaining these SIFT indexes has performance overhead.
Few advantages of NCCI:
Improved Query Performance: Nonclustered columnstore indexes can significantly enhance the performance of queries, particularly those involving large volumes of data and complex aggregations. This is because columnstore indexes store data in a columnar format rather than a row-based format, which is optimized for reading and aggregating large datasets.
Efficient Data Compression: Columnstore indexes use advanced compression techniques that can reduce the amount of storage space required.
Faster Data Retrieval: By optimizing how data is read from storage, columnstore indexes can speed up data retrieval times for analytical queries. This is especially beneficial for reporting and business intelligence tasks that require quick access to large volumes of data.
Reduced I/O Operations: Because columnstore indexes only need to read the relevant columns for a query rather than entire rows, they can reduce the amount of I/O operations required.
Support for Analytics and Reporting: Nonclustered columnstore indexes are particularly well-suited for analytic queries, which often involve operations like summing, averaging, and other aggregate functions. This makes them valuable for generating reports and performing data analysis in Business Central.
Columnstore vs. Rowstore: NCCIs are columnstore indexes, whereas traditional indexes (SIFT) are rowstore.
Columnstore indexes are better suited for analytical workloads, while rowstore indexes are optimized for transactional workloads.
A rowstore is data that’s logically organized as a table with rows and columns, and physically stored in a row-wise data format.
A columnstore is data that’s logically organized as a table with rows and columns, and physically stored in a column-wise data format.
How to use NCCI
AL Developer can now define the NCCI instead of SIFT by using the new keyword columnstore in Table definition as shown in below example
The SumIndex field “Qty on Hand”, “Qty on Transit”, “Reorder Qty” are defined on the table definition with Key fields “Item No.”, Location
For more details refer to below link
