Indexing data for performance

Path: Development > Programming in the AL language > Tables and data > Indexing data for performance · tier official · system platform · narrative reviewed (checked by Opus)

Overview

This section deals with two ways to speed up sum calculations on large tables, which matter for FlowFields and queries. SIFT uses SumIndexFields tied to keys and implemented as indexed views in SQL Server. NCCI uses the SQL Server nonclustered columnstore index feature, for analytical queries, with lower maintenance overhead and less database locking than SIFT keys.

Key points

• SIFT keys create indexed views in SQL Server that use GROUP BY to speed up aggregates on numeric columns.
• SIFT has a maintenance cost, and the MaintainSIFTIndex property is a factor in managing that cost.
• SIFT performance depends on index design and field positioning, and should be tested.
• For tuning and tracing, SQL Server tools such as the profiler help measure SIFT overhead and decide whether to combine SIFT keys. NCCI has its own tuning and tracing guidance with SQL Server tracing and columnstore index maintenance.
• NCCI supports fast sums on decimal, integer and biginteger columns, and helps FlowFields and query performance.
• NCCI in Business Central excludes BLOB fields and reduces database locking compared to SIFT keys.
• Assess whether an NCCI is needed and choose its fields carefully. Consider disabling SIFT indexes, and test performance after any change to the NCCI structure.
• A migration page explains when and how to replace SIFT indexes with NCCI on custom tables to reduce maintenance overhead and improve query performance.

Learn pages

• Migrating from SIFT to Nonclustered Columnstore Indexes (NCCIs): Explains how you can change from using SIFT keys to nonclustered columnstore indexes in Business Central tables.
• NCCI Tuning and Tracing: Explains how to tune and trace nonclustered columnstore indexes in Business Central.
• Nonclustered Columnstore Indexes (NCCI): Provides an introduction to nonclustered columnstore indexes in Business Central.
• Nonclustered Columnstore Indexes (NCCI) and Performance: This article looks at the factors you must take into consideration when you deal with nonclustered columnstore indexes and performance.
• Nonclustered Columnstore Indexes (NCCIs) and SQL Server: Explains how nonclustered columnstore indexes in Business Central tables work with SQL Server.
• SIFT and Performance: This article looks at the factors you must take into consideration when you deal with SIFT and performance.
• SIFT and SQL Server: Explains how SIFT in Business Central tables work with SQL Server.
• SumIndexField Technology (SIFT): Provides an introduction to SIFT indexes in Business Central.
• Tuning and Tracing: Explains how to tune and trace SIFT indexes in Business Central.

Videos and posts

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• Dynamics 365 Business Central: use sequential GUIDs when possible. (community post): “Sequential GUIDs insert data in order, keeping related records clustered”
• Dynamics 365 Business Central 2026 Wave 1. What’s New in Performance. (community post): “Removing unused indexes can speed up writes, and enabling needed ones can speed up reads”
• Debunking Myths related to Table Structure in Dynamics 365 Business Central 2026 Wave 2 (community post): “The 8KB SQL Server limit applies only to primitive data types”
• BC 29 lets a single index span base table and table extension fields (community post): “Business Central 29 allows table extension keys to span both base table and extension fields in a single index”
• Planning Table Indexes for the Best Performance (community post): “Clustered indexes define physical row ordering and are used by Get()”
• Troubleshooting Series – Ep3 – Missing Indexes (community post): “Indexes can only be added by development, through an AppSource app”
• What’s New: Enhanced Index Management (2026 release wave 1) (video): “Enhanced Index Management database performance storage optimization index lifecycle”

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.