Business Central 29.0: Major Change to Table Extensions & SQL.

Overview

The video walks through how table extension data has been stored in SQL across three stages: separate SQL storage for extension data, extension companion tables, and the Business Central 29 model where one AL table maps to one SQL table. It explains why Microsoft made the change and what it means for developers.

The presenter covers the main effects: indexes can span base and extension fields, and gets and finds can load from one table instead of joining companion tables. He argues that partial loading with set load field is still needed, demonstrates SQL queries with and without partial load, and says direct SQL should be avoided in favor of AL, APIs or queries. The presenter notes that Business Central 29 is in public preview, that he had no access to the SQL server in the cloud, and that the exact threshold for the new column-limit compiler warning is not known to him.

Key points

• In Business Central 29, fields from table extensions are stored in the same database table as the base table, replacing the extension companion table model.
• Storage has gone through three stages: a separate SQL table per extension, a shared $ext companion table, and now a single unified table.
• Keys/indexes can now combine fields from a base table and its table extensions, which the earlier two storage models did not allow.
• Finds and other reads can be faster because extension fields are in one table and no join to companion tables is needed.
• Keep using set load field (partial loading) in version 29 so only the needed fields are fetched and queries stay smaller.
• Do not read or write SQL directly; use AL, Business Central APIs or queries, and treat the physical SQL schema as an implementation detail that may change again.
• BC29 adds compiler warnings when tables or table extensions with many normal fields approach the SQL column limit; the speaker does not know the exact threshold or behavior and proposes testing it.

Features

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 “item table” at 4:25
• table 32 “Item Ledger Entry” at 16:59

Not found in BC28-30: table “item table”.

Quotes

• 1:43 “business 29 introduces a major change to the data model used for table extensions”
• 1:54 “fields from table extensions are stored in the same database table as the base table instead of being stored”
• 2:31 “business 29 is currently in public preview. The platform behavior in Microsoft documentation may change before general availability.”
• 9:28 “business center 29 onrem version is not available and on business center cloud I don’t have access to the SQL server”
• 10:03 “one business central table, AL table is equivalent to one SQL table. No companion table, no table per extension.”

Disclaimers in the video

• 2:31 preview: business 29 is currently in public preview. The platform behavior in Microsoft documentation may change before general availability.
• 9:28 preview: Business center 29 onrem version is not available and on business center cloud I don’t have access to the SQL server
• 25:40 other: it’ll be able to detect table approaching SQL column limit. Now this is very interesting. It is as new to …
• 27:22 other: I’m very sure that they would not have taken this decision in isolation of changing the underlying architecture. But I …
• 28:05 other: the Microsoft learn document or the release note very clearly says that it’ll give you a compiler warning as it …
• 28:24 other: I don’t know what’s the exact threshold what kind of compiler warning will be generated does a warning apply appears …