Every AL key becomes a SQL index, and every FlowField needs a SIFT key behind it. The decisions that make a table fast or slow for its life: the primary key, secondary keys for your filters, SumIndexFields, IncludedFields, and when to turn an index off.
Keys in AL are indexes in SQL, and they are the one design decision that decides whether a table is fast at a million rows. The base application's hot tables have carefully chosen keys; your extension's tables and the keys you add to Microsoft's need the same care.
The first key is the primary key and, by default, the clustered index: the physical order of the rows. For entry tables, "Entry No." as an incrementing integer is right, because inserts go at the end. For master tables, the code field. Avoid wide composite primary keys and never put a text field in one. Clustered = true can move the clustering to another key if the primary key is a poor physical order (rare; think first).
A SetRange on fields that are not the leading columns of any key is a table scan. Add a key for each distinct way the table is queried in a hot path, with the most selective filter field first and the fields you sort by after it. Do not add one for every field; each index costs on insert and update. Three to six secondary keys is typical for a busy table.
keys
{
key(PK; "Entry No.") { Clustered = true; }
key(ItemLoc; "Item No.", "Location Code", "Posting Date")
{
SumIndexFields = Quantity, "Cost Amount (Actual)";
IncludedFields = "Document No.";
}
key(Doc; "Document No.", "Posting Date") { MaintainSqlIndex = false; }
}A FlowField that sums or counts, and every CalcSums, is fast only if a key exists whose leading fields cover the filter and whose SumIndexFields include the summed field. Then SQL maintains an indexed view of the totals and the read is instant. Without it, every CalcFields is a full aggregate. Put SIFT on the key that matches how the FlowField is filtered, and keep MaintainSiftIndex = true for anything read often; set it false for a key used only by a nightly batch.
Non-key columns added to the index so that a query using SetLoadFields on just those columns can be served from the index without touching the row. For a lookup that always reads two fields by a filter, an index with those two included is the fastest read the platform can do.
MaintainSqlIndex = false keeps the key in AL (for sorting in code and pages) without a physical index. Use it for keys that exist only to sort a rarely used page, on tables with heavy insert volume. Unique = true on a secondary key enforces uniqueness at the database.
A table extension can add keys to a base table, which is how you make your extension's filter on Item Ledger Entry fast. Add them sparingly: the base tables are already heavily indexed and each addition is paid on every insert in every company.
Telemetry's long-running SQL signal (RT0005) gives you the statement and the AL object; the SQL has a WHERE clause with no matching index. Add the key, deploy, watch the signal go quiet. Before adding, check the existing keys of the table in the symbol browser; the base application often already has one that nearly matches, and reordering your filter to use it is free.