Creating TableExtensions in BC29 like we're back in NAV (But Business Central)
The video covers table extension storage in Business Central 29. Extension fields are no longer kept in a separate SQL table but are stored in the same SQL table as the base table. This lets a table extension define cross-app keys that combine base table fields (for example, customer No.) with the extension's own fields. It requires runtime 18 and platform 29. Trade-offs: uninstalling an app with delete data now drops columns, and SQL's limit of about 1000 columns per table applies.
- Introduction and BC29 table extensions overview
- History: NAV to BC extension storage evolution
- Performance optimization with compact storage and load fields
- Cross-app keys limitation and BC29 solution
- Demonstration: creating table extensions with cross-app keys
- Impact on performance and workarounds no longer needed
- Practical challenges and limitations of the new approach
- Conclusion and wrap-up
Overview
Erik Hougaard walks through how table extension storage has changed from NAV through Business Central. Earlier versions used a separate table per app. A later change (around BC24, version uncertain) moved all app fields into one extra table per base table and added load fields to cut join cost.
In BC29, table extensions are no longer stored in separate tables. The video demonstrates creating a table extension with a key that combines base table fields and extension fields from different apps, which was not possible in BC12 to BC28. It also covers the expected performance impact and practical limitations of the new approach.
Key points
- In BC29, table extension fields are no longer stored in a separate table but in a single SQL table together with the base table.
- From BC12 onward, every app extending a base table got its own SQL table, so heavily extended tables such as sales header and lines needed joins across many tables.
- Load fields let developers choose which fields to retrieve so that unused app tables are left out of the join, but it is hard to predict which fields can safely be excluded.
- Compact extension storage (around BC24, version uncertain) put all extension fields into one extra table, so only two tables were joined, but it still did not support cross-app keys.
- In BC29, a table extension can define keys that combine base app fields (for example, customer No.) with its own fields. The demo adds such a key on a Customer table extension.
- This requires runtime 18 and platform 29.
- Workarounds such as copying a base field like customer number into the extension to get usable keys are no longer needed.
Features
| Feature | Status | At |
|---|---|---|
| Table extensions stored in single SQL table | generally available (roadmap Faster data loading with improved data model for table extensions), demoed | 4:48 |
| Cross-app keys in table extensions | generally available (roadmap Developers can define indexes that span fields from a base table and its table extensions), demoed | 4:07 |
| Load fields for selective field retrieval | status not stated | 2:25 |
| Compact extension table storage | status not stated | 3:32 |
A status with a roadmap link comes from the Microsoft 365 roadmap feature this part of the video covers (matched by Haiku; links Opus dropped are not used); other statuses need a status word in the video itself.
AL objects mentioned
As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.
- table “customer table” at 7:28
- table “table 36” at 1:59
- table “table 37” at 1:59
- table “sales head” at 1:59
- table “sales lines” at 1:59
- table “customer ledger entries” at 4:37
Not found in BC28-30: table “customer table”, table “table 36”, table “table 37”, table “sales head”, table “sales lines”, table “customer ledger entries”.
Quotes
- 0:23 “table extensions are no longer stored in the separate table”
- 4:07 “Microsoft never got around to support cross app uh keys. And and when I say cross app, I I the the main use case”
- 4:48 “now we arrive at BC 29 and Microsoft has added so we have the base table and we have the table with with the”
- 5:01 “we have the table with with the extension fields and now this is stored as a single table on SQL.”
- 5:48 “if we’re not on runtime 18 and if you’re not on a uh in reality platform 29”
Disclaimers in the video
- 3:21 subject-to-change: with BC, I can’t even remember I think it was BC 24. Don’t quote me on the number here
- 5:48 subject-to-change: if we’re not on runtime 18 and if you’re not on a uh in reality platform 29 uh you’re not getting this
Presenters (as heard): Eric.