BC29 finally lets your custom fields share an index

Almost every Business Central customer filters standard fields and custom fields together: orders for a delivery route on a given date, or items in a custom category from one vendor. Until now, BC couldn’t build one index that covered both kinds of field, so those searches were slower than they needed to be.

Business Central 2026 release wave 2 (BC29) removes that limit. A key in a table extension can now mix fields from the base table and fields from the extension. For anyone with a heavily extended system, this is one of the biggest performance changes in years.

This post covers what an index is (with no SQL needed), why the old limit existed, what changes in BC29, and what to do about it.

The short answer

• An index is a pre-sorted shortcut that lets BC find records without reading the whole table.
• Before BC29, extension fields were stored in a separate SQL table, so one index couldn’t contain both standard and extension fields.
• In BC29, extension fields are stored in the same table as the standard fields, and a key in a tableextension can include both.
• Searches that filter standard and custom fields together can now get an index built exactly for them. Expect the biggest gains on large tables.

What an index is, explained with a library

Picture a library where every book is shelved by author surname. If you know the author, you walk straight to the right shelf. That ordering is the table’s primary key: the one order the data is physically stored in.

Now someone asks for every crime novel. The author order is no help. You’d have to walk every aisle, take each book down and read the back cover. On a database that’s a table scan, and on a big table it’s slow.

So the librarian puts a coloured sticker on every spine: red for crime, green for sci-fi, blue for history. Now you can walk the aisles and pick out the red spines at a glance, without opening a single book. Better still, keep a list at the front desk of where every red sticker is, and you can go straight to them.

That’s an index. The books stay in author order, but a second, pre-built way of finding them exists for the questions people ask most.

Indexes aren’t free, and the library shows why. Every new book that arrives needs its sticker before it goes on the shelf, and the front desk list needs updating. Add a sticker for genre, another for decade, another for setting and another for page count, and new books queue up at the door. In BC terms, every index makes inserts and modifications a little slower and uses more storage. The skill is adding the indexes people really need, not one for every possible question.

Why standard and extension fields couldn’t share an index

Here’s where extensions come in. Say the library adds a new piece of information, the county each book is set in, but the notes are kept in a separate binder in the back office rather than on the catalogue cards.

Someone asks for crime novels set in Yorkshire. You can find the red stickers quickly, and you can search the binder for Yorkshire, but there’s no single sticker for “crime and Yorkshire”, because the two facts live in different places. You find all the crime novels, then check each one against the binder.

That’s how BC worked from 2019 release wave 2 (BC15) until BC29. Fields added by a tableextension were stored in a separate SQL “companion table” next to the base table. A key in a table extension could use base table fields, or fields from that extension, but not both in the same key, because SQL can’t build one index across two tables.

So when code filtered the Sales Header on “Document Type” and a custom “BCN Delivery Route” field, SQL had to join the two tables and could only use an index on one side. On a table with millions of rows, that’s the difference between an instant result and a spinning wheel.

The usual workaround was to copy a standard field into the extension, keep it in sync with event subscribers, and build the key on the copies. It worked, but it meant duplicated data and more code to maintain.

What changes in BC29

BC29 brings a new data model for table extensions. Extension fields are now stored in the same SQL table as the base table’s fields, so the binder notes are written on the catalogue cards themselves. Because everything lives in one table, a key in a table extension can now mix both.

Here’s the delivery route example. The extension adds the field and a key that combines it with two standard Sales Header fields. The BCN prefix is the affix I use for examples on this blog. Fields, keys and objects that an extension adds to a standard table need your own registered affix, so they can’t clash with Microsoft’s or another app’s:

tableextension 50100 "BCN Sales Header Delivery" extends "Sales Header"
{
    fields
    {
        field(50100; "BCN Delivery Route"; Code[20])
        {
            Caption = 'Delivery Route';
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        // BC29 and later: standard and extension fields in one key
        key(BCNRouteByShipDate; "Document Type", "BCN Delivery Route", "Shipment Date")
        {
        }
    }
}

The code that uses it doesn’t change at all. SQL Server picks the best index for the filters, so there’s no need for SetCurrentKey just to get the index used:

codeunit 50100 "BCN Delivery Route Mgt."
{
    Access = Internal;

    procedure CountOrdersOnRoute(RouteCode: Code[20]; ShipmentDate: Date): Integer
    var
        SalesHeader: Record "Sales Header";
    begin
        SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
        SalesHeader.SetRange("BCN Delivery Route", RouteCode);
        SalesHeader.SetRange("Shipment Date", ShipmentDate);
        exit(SalesHeader.Count());
    end;
}

Before BC29, that key wouldn’t compile. In BC29, SQL can go straight to the matching orders through one index, with no join and no scan.

The same applies to the other key properties. SumIndexFields and IncludedFields can also mix standard and extension fields, so a FlowField that sums a standard amount filtered by a custom field can finally get its own SumIndexField Technology (SIFT) index.

Gotchas

And the rule from the library still holds: every key slows down writes a little. Add the ones that match real, frequent searches on big tables, and measure the difference on the customer’s own data before and after. A key that looks sensible on a demo company can do nothing, or make posting slower, on a live one.

Finding the indexes that are missing

You don’t have to guess where indexes would help. The Database Missing Indexes page in BC lists the indexes SQL Server thinks would have helped the queries it has run, including the table, the extension and the columns involved. It’s a list of suggestions, not a to-do list, but it’s an excellent starting point.

Until BC29, a lot of those suggestions were impossible to act on, because they mixed standard and extension fields. They’re worth a fresh look now. Telemetry and slow-page reports from users fill in the rest.

What this means for users

If you filter lists or run reports on custom fields alongside standard ones, those screens can get much faster in BC29, sometimes going from a long wait to near instant. Nothing changes in how you work. Someone has to add the right index first, though, and that’s where you come in, because the hardest part of a performance problem can be knowing it’s there.

If a page, report or search is slow, especially when you filter on a particular field, tell your partner or your BC team. Say which page, which filters you used and roughly how long it took. You don’t need to know whether an index is missing. That’s our job to find out. A slow screen that everyone has learned to live with never reaches a developer, and we can only fix the ones we know about.