Hi All,
Business Central 29.0 introduces a major change to the data model used for table extensions.With the new model, fields from table extensions are stored in the same database table as the base table instead of being stored using the previous extension companion-table model.
This change is important for Business Central developers because it affects how table extensions are represented in the database, enables new indexing scenarios, and can improve the performance of database operations involving table extensions.
Note: Business Central 29.0 is currently in public preview. The platform behavior and Microsoft documentation may change before general availability.
What is changing in the Business Central table extension data model?
To understand the significance of Business Central 29.0, it helps to look at how table extension data storage has evolved over time.
There have been several different approaches to representing table-extension data in the underlying SQL database.
The evolution can be simplified into three stages:
- Separate SQL storage for extension data
- A shared extension companion-table model
- The new Business Central 29.0 model where extension fields are stored in the same SQL table as the base table
Let's look at each stage.
How was table extension data stored previously?
The earlier model: separate SQL storage
In earlier versions of Business Central, extension data could be represented using separate SQL tables associated with extensions.
Conceptually, the relationship looked something like this:
Base AL Table
|
+---- SQL Base Table
Extension A
|
+---- SQL Extension Table
Extension B
|
+---- SQL Extension Table
The important point is that the extension fields were physically separated from the base table data at the SQL storage level.
For developers, this was an implementation detail managed by the Business Central platform.
However, as the extension model evolved, Microsoft changed how this data was represented.
What was the extension companion-table model?
Microsoft subsequently moved toward a model based around extension companion tables.
Conceptually, the structure looked like this:
Base AL Table
|
+---- SQL Base Table
|
+---- Extension Companion Table
|
+---- Extension A fields
+---- Extension B fields
+---- Extension C fields
Instead of creating a separate SQL table for every extension, extension fields from different extensions could be stored in a shared companion table.
This model provided a different way of handling extension data while keeping the physical database implementation managed by the platform.
But it still meant that extension fields were not physically stored in the same SQL table as the base table.
That is what changes again with Business Central 29.0.
What is new in Business Central 29.0?
Business Central 29.0 introduces a new data model for table extensions.
With the new model, fields from table extensions are stored in the same database table as the base table.
Conceptually, you can think about it like this:
AL Table
|
+------------+------------+
| |
Base fields Extension fields
| |
+------------+------------+
|
SQL Table
In other words:
One AL table corresponds to one database table, with fields contributed by table extensions stored in that same database table.
Microsoft specifically describes the new data model as storing all fields on an AL table in the same database table.
This is one of the important platform changes introduced with Business Central 29.0.
Why did Microsoft change the table extension data model?
The most interesting part of this change is not simply where the fields are stored.
The new model enables functionality that wasn't possible in the same way under the previous companion-table approach.
Microsoft currently highlights two major benefits:
- Developers can define indexes that span fields from a base table and its table extensions.
- Database operations involving table extensions can benefit from faster data loading.
Let's look at both.
Can Business Central indexes now span base-table and extension fields?
Yes.
One of the important capabilities introduced with the new table-extension data model is the ability to define indexes that span fields from the base table and its table extensions.
Conceptually, imagine a base table containing:
Customer No.
Name
Country
and an extension contributing:
Customer Category
With the new model, developers can define a key that involves fields from both the base table and the table extension.
This is significant because the fields are now stored in the same underlying database table.
It gives developers more flexibility when designing data access patterns for customized Business Central solutions.
Does the new table extension model improve performance?
Microsoft states that the new data model provides faster data loading with an improved model for table extensions.
The reason is straightforward.
With the previous companion-table approach, database operations involving extension fields could involve separate storage.
With the new model, the fields belonging to the AL table are stored together in the same database table.
Conceptually:
Previous Model
Base Table
|
+---- Base fields
Companion Table
|
+---- Extension fields
New Model
Single SQL Table
|
+---- Base fields
+---- Extension fields
This can simplify database operations involving table-extension fields.
Microsoft describes this as providing faster performance on database operations that involve table extensions.
However, developers should not interpret this as meaning that every database operation will automatically become faster.
Actual performance still depends on factors such as:
- Which fields are requested
- Which fields are actually consumed
- The indexes available
- The size of the records
- The query pattern
- The number of records being processed
- How AL code accesses the data
This is why partial record loading remains important.
Does the new SQL model change how developers should use partial loading?
No.
The underlying storage model is changing, but partial record loading remains an important development consideration.
Business Central developers should continue thinking about which fields their code actually needs.
For example:
MyRecord.SetLoadFields("No.", Description);
MyRecord.FindFirst();
The code explicitly tells Business Central that the application only needs specific fields for the operation.
If additional fields are accessed later, Business Central may need to retrieve those fields as well.
This means the fields your AL code consumes can still influence the database access pattern.
The important takeaway is:
The new SQL storage model does not eliminate the importance of partial records.
Instead, developers should continue to understand how their AL code influences the fields that Business Central needs to retrieve.
Should Business Central developers change their solutions because of this SQL change?
This is one of the most important questions.
The answer is:
No — don't redesign your solutions around the physical SQL schema.
The fact that Microsoft is changing how table-extension data is physically stored is actually a good demonstration of why Business Central developers should avoid depending on the underlying SQL implementation.
Your solution should continue to use supported Business Central development abstractions.
That means:
- Use AL.
- Use Business Central APIs.
- Use supported query mechanisms.
- Use records and pages appropriately.
- Use supported integration patterns.
- Use partial records where appropriate.
- Avoid direct SQL dependencies.
The physical database model can change.
Your application should continue to work because it depends on the Business Central application layer rather than the implementation details of the database.
What happens to direct SQL solutions?
This is where the change becomes particularly important.
Suppose a solution makes assumptions about how table-extension data is stored:
"Extension Companion Table"
|
+---- Custom SQL Query
That solution is tightly coupled to an implementation detail.
When Microsoft changes the storage model, such a solution can become invalid or require redesign.
The better architecture is:
Business Central
|
+---- AL
|
+---- APIs
|
+---- Supported Queries
|
+---- Supported Integration Patterns
rather than:
Business Central
|
+---- Direct SQL
|
+---- Assumptions about physical schema
The BC29 table-extension change is a good reminder that the physical SQL schema should not be treated as a stable application contract.
What about SQL column limits?
Business Central 29.0 introduces another feature that is particularly interesting in the context of this new data model:
Detect tables approaching SQL column limits.
Microsoft states that developers can receive compiler warnings when tables or table extensions contain many normal fields.
The purpose is to help developers identify data models that are approaching SQL extensibility limits.
This is particularly relevant because the new table-extension model stores extension fields in the same database table as the base table.
Conceptually, think about a table like this:
Base Table
Base Field 1
Base Field 2
Base Field 3
...
Base Field N
Extension A
Extension Field 1
Extension Field 2
...
Extension Field N
Extension B
Extension Field 1
Extension Field 2
...
Extension Field N
Under the new model, these fields contribute to the same underlying database table.
Therefore, the total number of fields in a heavily extended table becomes an important architectural consideration.
How exactly do the new SQL column-limit warnings work?
This is where I think there is an opportunity for a separate technical experiment.
The Microsoft Learn documentation currently states that Business Central can provide compiler warnings when tables or table extensions contain many normal fields.
However, the high-level documentation doesn't answer every practical question a developer might have.
For example:
- What is the exact threshold?
- What compiler diagnostic is generated?
- Does the warning appear on the base table?
- Does it appear on a table extension?
- Does it consider fields across multiple extensions?
- What happens when the limit is exceeded?
- Is the result a warning or an error?
- Does the behavior differ between different field types?
- How should developers respond to the warning?
These are excellent questions for a hands-on experiment.
Should there be a separate video testing the SQL column-limit warnings?
Yes — this could make a very useful follow-up developer video.
Rather than simply explaining the documentation, the experiment could create progressively larger tables and table extensions.
For example:
Test 1
Base table with a large number of fields
Test 2
Base table + one table extension
Test 3
Base table + multiple table extensions
Test 4
Approaching the documented SQL limit
Test 5
Exceeding the limit
The video could then capture the exact compiler diagnostics produced by Business Central 29.0.
That would turn the documentation statement into a practical developer-focused test.
What should Business Central developers take away from BC29?
There are several important takeaways.
1. Understand the new data model
Table-extension fields are now stored in the same database table as the base table.
This is a significant platform change.
2. Cross-table-extension indexes become possible
Developers can define indexes spanning fields from the base table and table extensions.
3. Data loading can improve
Microsoft states that the new model provides faster performance for database operations involving table extensions.
4. Continue using partial records
The new data model does not mean developers should load every field.
Continue to use partial records appropriately and understand the fields your code actually consumes.
5. Don't depend on direct SQL
The physical SQL implementation is a platform detail.
Use AL, APIs, queries, and supported Business Central application patterns instead.
6. Pay attention to SQL column limits
The new compiler diagnostics can help identify data models that are approaching SQL extensibility limits.
Frequently Asked Questions
What changed in Business Central 29.0 for table extensions?
Business Central 29.0 introduces a new data model where fields contributed by table extensions are stored in the same database table as the base AL table.
Are Business Central companion tables being removed?
The new table-extension data model replaces the previous companion-table approach for storing table-extension fields. Developers should treat the physical SQL schema as an implementation detail rather than something their applications should depend on.
Can Business Central indexes now include fields from table extensions?
Yes. Business Central 29.0 introduces the ability to define indexes that span fields from a base table and its table extensions.
Does the new data model make Business Central faster?
Microsoft states that the improved table-extension data model provides faster performance for database operations involving table extensions. Actual performance depends on the workload, query pattern, indexes, and fields being accessed.
Does partial record loading still matter in Business Central 29.0?
Yes. Developers should continue using partial records appropriately. The new storage model does not eliminate the importance of loading only the fields required by the application.
Should developers use direct SQL now that extension fields are stored in the same table?
No. The physical database structure is still an implementation detail. Business Central solutions should use supported AL, APIs, queries, and integration patterns rather than depending directly on SQL schema details.
Watch the Video
In this video, I explain the evolution of the Business Central table-extension data model, from separate extension storage to the companion-table model and finally to the new Business Central 29.0 model.
I also explain the impact of this change on:
- Table extensions
- SQL storage
- Indexes
- Database performance
- Partial record loading
- Direct SQL solutions
- SQL column limits
- Business Central development architecture
Final Thoughts
The table-extension changes in Business Central 29.0 are more than an internal database optimization.
The new model enables new indexing scenarios, can improve data-loading performance, and changes how developers should understand the relationship between AL tables, table extensions, and the underlying database.
But there is an equally important architectural lesson:
Don't build your Business Central solution around assumptions about the physical SQL database.
Microsoft can change the underlying data model while keeping the supported AL and Business Central application model intact.
The move from separate extension storage to companion tables and now to a unified table model is a good example of why Business Central developers should build against supported abstractions.
And the new SQL column-limit diagnostics could be an interesting area for further experimentation.
If you would like to see a practical experiment testing the new SQL column-limit warnings in Business Central 29.0, let me know in the comments.
Subscribe to the Saurav Dhyani YouTube channel for Business Central technical tutorials, AL development, APIs, extensions, new features, and practical implementation guidance.
You can also connect with me on:
Regards,
Comments
Post a Comment