System Fields, Primary Keys & Indexing in Integrations

Integrations rely heavily on fast lookups, accurate matching, and efficient delta detection. For this to work properly, the fields used in these operations must be indexed. This page explains what system fields and primary keys are, what happens when they are not indexed, and how to ensure optimal indexing.

1. What Are System Fields?

System fields are internal fields used by applications to manage and track record changes. Common examples include:

• ModifiedDate/LastWriteTime/UpdatedAt
• CreatedDate
• RowVersion/Timestamp
• System IDs/GUIDs
• External IDs used for integration matching

Integrations depend on these fields to detect changes (delta loads), match records, and avoid reprocessing unchanged data.

2. Are Primary Keys Indexed by Default?

Yes, primary Keys (PKs) are always indexed by default in modern systems such as Business Central, NAV, Dynamics 365 Finance & Operations, Salesforce, and SQL databases.
PK fields rarely cause performance issues. The real issues appear when non-PK fields used in integrations are not indexed.

3. What Happens When Fields Are Not Indexed?

If system fields or MATCHON/lookup fields are not indexed, you may experience:

• Slow transfers: large table scans instead of fast lookups
• Timeouts: filtering on unindexed fields exceeds API or SQL limits
• Missed deltas: updated records not captured due to slow queries
• High system load: increased CPU, IO, and memory consumption

Indexing ensures the system can quickly locate only the records needed.

4. Which Fields Should Be Indexed?

Index any field that participates in integration logic, such as:

• Delta fields: ModifiedDateTime, LastWriteTime, RowVersion, SystemModstamp
• MATCHON fields: business keys used to locate corresponding records
• Lookup fields: ExternalId, NAV_ID__c, integration reference fields
• Filter fields: fields used in WHERE conditions or OData/SOQL filters

If a field is used to filter, match, or identify records—it should be indexed.

Important Note: Avoid Over-Indexing

Indexing improves read performance, but:

• Each index adds overhead on write operations (insert, update, delete).
• Too many indexes can degrade overall performance.

Index only what is necessary. The decision to index a field should be a calculated one, based on:

• How frequently the field is used in queries
• How critical the field is to integration logic
• The balance between read performance and write performance

5. How to Index Fields (Summary)

Business Central / NAV

• Add a Key in AL/CAL
• Ensure Enabled = Yes and Maintained = Yes (API/OData)

Dynamics 365 Finance & Operations

• Add an Index in the table definition
• Include delta and filter fields

SQL Databases

CREATE INDEX IX_Table_ModifiedDate ON dbo.Table (ModifiedDate);

Salesforce

• Mark fields as External ID to auto-index
• Avoid filtering on non-indexed long text fields

Summary

• PKs are indexed automatically → no action needed.
• Index only when necessary.
• Proper indexing boosts performance and prevents timeouts.
• Avoid over-indexing by evaluating your application’s query patterns.