<turbo-stream action="update" target="wiki_card"><template>

<h1>System Fields, Primary Keys &amp; Indexing in Integrations</h1>
<div class="trix-content"><p>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.<br><br></p><h2><strong>1. What Are System Fields?</strong></h2><p>System fields are internal fields used by applications to manage and track record changes. Common examples include:</p><ul><li><strong>ModifiedDate/LastWriteTime/UpdatedAt</strong></li><li><strong>CreatedDate</strong></li><li><strong>RowVersion/Timestamp</strong></li><li><strong>System IDs/GUIDs</strong></li><li><strong>External IDs used for integration matching</strong></li></ul><p>Integrations depend on these fields to detect changes (delta loads), match records, and avoid reprocessing unchanged data.<br><br></p><h2><strong>2. Are Primary Keys Indexed by Default?</strong></h2><p>Yes, primary Keys (PKs) are always indexed by default in modern systems such as Business Central, NAV, Dynamics 365 Finance &amp; Operations, Salesforce, and SQL databases.<br>PK fields rarely cause performance issues. The real issues appear when non-PK fields used in integrations are not indexed.<br><br></p><h2><strong>3. What Happens When Fields Are Not Indexed?</strong></h2><p>If system fields or MATCHON/lookup fields are not indexed, you may experience:</p><ul><li><strong>Slow transfers:</strong> large table scans instead of fast lookups</li><li><strong>Timeouts:</strong> filtering on unindexed fields exceeds API or SQL limits</li><li><strong>Missed deltas:</strong> updated records not captured due to slow queries</li><li><strong>High system load:</strong> increased CPU, IO, and memory consumption</li></ul><p>Indexing ensures the system can quickly locate only the records needed.<br><br></p><h2><strong>4. Which Fields Should Be Indexed?</strong></h2><p>Index any field that participates in integration logic, such as:</p><ul><li><strong>Delta fields:</strong> ModifiedDateTime, LastWriteTime, RowVersion, SystemModstamp</li><li><strong>MATCHON fields:</strong> business keys used to locate corresponding records</li><li><strong>Lookup fields:</strong> ExternalId, NAV_ID__c, integration reference fields</li><li><strong>Filter fields:</strong> fields used in WHERE conditions or OData/SOQL filters</li></ul><p>If a field is used to filter, match, or identify records—it should be indexed.<br><br><strong><u>Important Note: Avoid Over-Indexing</u></strong></p><p>Indexing improves read performance, but:</p><ul><li>Each index adds overhead on write operations (insert, update, delete).</li><li>Too many indexes can degrade overall performance.</li></ul><p>Index only what is necessary. The decision to index a field should be a calculated one, based on:</p><ul><li>How frequently the field is used in queries</li><li>How critical the field is to integration logic</li><li>The balance between read performance and write performance</li></ul><h2><strong>5. How to Index Fields (Summary)</strong></h2><h3><strong>Business Central / NAV</strong></h3><ul><li>Add a <strong>Key</strong> in AL/CAL</li><li>Ensure <strong>Enabled = Yes</strong> and <strong>Maintained = Yes</strong> (API/OData)</li></ul><h3><strong>Dynamics 365 Finance &amp; Operations</strong></h3><ul><li>Add an <strong>Index</strong> in the table definition</li><li>Include delta and filter fields</li></ul><h3><strong>SQL Databases</strong></h3><pre><code>CREATE INDEX IX_Table_ModifiedDate ON dbo.Table (ModifiedDate);<br></code><br></pre><p><strong>Salesforce</strong></p><ul><li>Mark fields as <strong>External ID</strong> to auto-index</li><li>Avoid filtering on non-indexed long text fields</li></ul><h2><strong>Summary</strong></h2><ul><li><strong>PKs are indexed automatically</strong> → no action needed.</li><li>Index <strong>only</strong>&nbsp;when necessary.</li><li>Proper indexing boosts performance and prevents timeouts.</li><li>Avoid over-indexing by evaluating your application’s query patterns.</li></ul></div>
</template></turbo-stream>

