Business Central 29 introduces compiler warnings for tables and table extensions that contain many normal fields and are approaching SQL extensibility limits.
The interesting question is what happens to the practical field limit now that Business Central 29 stores fields from table extensions in the same database table as the base table.
What changed in Business Central 29?
Business Central 29 introduces a new data model for table extensions. Microsoft describes the change as storing all fields from an AL table and its table extensions in the same database table. Microsoft says this improves performance for database operations involving table extensions. At the same time, Business Central 29 introduces compiler warnings that help developers identify tables approaching SQL column limits.
That combination makes the field-limit question much more interesting.
In previous versions, including Business Central 28, table extensions used companion-table storage for extension fields. With the new model in Business Central 29, the fields are stored together in the same database table.
So the question becomes:
If extension fields are now stored in the same SQL table, how many fields can that table actually support?
Is the limit 300 fields?
This is where things get interesting.
A commonly mentioned number is 300 fields, but I don't think we should present 300 as the definitive maximum without qualification.
Microsoft's current documentation for Business Central object specifications lists 500 as the maximum number of fields in a record. The same documentation also lists a maximum record size of 8,060 bytes and notes that each variable-length text field accounts for 26 bytes in the record.
Those are important clues, but they don't completely answer the question raised by the new table-extension data model.
The new BC 29 feature is specifically described as warning when tables or table extensions contain many normal fields and are approaching SQL extensibility limits. Microsoft does not describe the feature simply as "300 fields is the maximum."
So there are at least two different concepts we need to keep separate:
The documented Business Central/SQL Server table specifications.
The threshold at which the new BC 29 compiler warning starts appearing.
The warning threshold should not automatically be interpreted as the absolute maximum.
Why did this become a bigger question in BC 29?
The reason is the change in how table-extension fields are stored.
Previously, extension fields could be stored in companion tables rather than directly in the base SQL table.
With Business Central 29, Microsoft introduced a new data model where all fields on an AL table are stored in the same database table.
That has an obvious consequence:
The combined schema matters.
Imagine a standard table containing 200 fields.
Now imagine several extensions collectively adding another 100 fields.
With the new storage model, those fields contribute to the same underlying database-table structure.
This makes SQL column capacity much more relevant to extension developers.
Does the field data type affect the limit?
This is one of the questions I wanted to investigate.
It's tempting to assume that there is simply a fixed number of columns:
"A table can have X fields, regardless of their data type."
But SQL Server has other limits besides the number of columns.
Business Central documents an 8,060-byte maximum record size and specifically notes that each variable-length text field accounts for 26 bytes in the record.
Business Central also documents different characteristics for field types. For example, a BLOB can be up to 2 GB, while text and code fields have their own documented characteristics.
That means the answer may not be as simple as counting field declarations.
There is a distinction between:
Maximum number of fields/columns
Maximum row/record size
Storage characteristics of different SQL data types
Business Central's own schema and extensibility rules
So testing only Integer or Code fields doesn't necessarily prove what happens with every possible field type.
My experiment: Integer and Code fields
For this video, I started with a deliberately simple experiment.
I created a table containing a large number of normal fields, using primarily Integer and Code fields.
The purpose was not to claim that this establishes the universal limit.
Instead, the goal was to establish a baseline:
How far can we go when the table consists largely of relatively straightforward field types?
From there, the next question is whether the behavior changes when we introduce other types such as Text and BLOB fields.
That's where the investigation gets more interesting.
What happens with Text and BLOB fields?
This is one area where I think more testing is required.
If a table is approaching SQL Server's record-size constraints, then field type and storage characteristics can become relevant even if the nominal number of fields is the same.
For example, a table containing hundreds of simple fields isn't necessarily equivalent to a table containing hundreds of larger or variable-length fields.
So a useful experiment would be to create separate test tables containing:
Integer fields
Code fields
Text fields
Larger Text fields
BLOB fields
Mixtures of these types
Then incrementally add fields and observe when the compiler warning appears and when the platform actually prevents the schema from being created or synchronized.
That would give us a much better picture than assuming that one field count applies identically to every data type.
What does the new compiler warning actually tell us?
This is perhaps the most useful part of the BC 29 feature.
Microsoft's feature description is deliberately focused on early detection.
The compiler warns when tables or table extensions contain many normal fields and are approaching SQL extensibility limits.
That means developers can get a signal before they reach the point where schema design becomes a hard blocker.
Think of the warning as a design-time alarm, rather than necessarily the final database limit.
This is particularly valuable for extension developers because your extension isn't always the only extension contributing fields to a table.
Why does this matter for AppSource and partner extensions?
Suppose you develop an extension that adds 30 fields to a standard table.
In your development environment, that might seem completely reasonable.
But a customer could have several other extensions that add fields to the same table.
With the BC 29 data model, those fields are part of the same underlying table structure.
That means extension developers need to think beyond the number of fields added by their own application.
The relevant question is:
How much schema space is left on the table after considering the entire extension ecosystem?
This is one reason the new compiler warning is useful.
Business Central 28 vs Business Central 29
The most important difference can be summarized like this.
Business Central 28
Table-extension fields used the previous companion-table storage model.
This reduced the need to put every extension field directly into the base SQL table.
Business Central 29
Microsoft introduced a new table-extension data model where fields from the base table and table extensions are stored in the same database table.
Microsoft also introduced compiler warnings to identify tables approaching SQL column limits.
This means BC 29 gives developers both:
A changed underlying storage model for table extensions.
Earlier feedback when the resulting table schema is getting close to SQL limits.
So, what is the actual limit?
This is the question I would not claim to have completely answered yet.
Microsoft's documented object specifications say 500 fields in a record, while also documenting an 8,060-byte maximum record size.
The new BC 29 feature introduces compiler warnings before tables approach the relevant SQL extensibility limits.
But the practical question is more nuanced:
Does every table reach the same field count?
Does the field data type change the practical limit?
How do Text and BLOB fields affect the calculation?
Exactly where does the BC 29 compiler warning appear?
These are excellent areas for experimentation.
What should developers take away?
The safest conclusion today is:
Don't assume that 300 fields is the definitive maximum for a Business Central table.
Instead:
Understand that BC 29 now stores table and table-extension fields together in the same database table.
Be aware of the documented SQL Server and Business Central limits.
Treat the new compiler warning as an early indication that your schema is approaching a limit.
Consider both field count and record-size characteristics.
Test different field types if your solution is pushing the limits.
Avoid designing an extension around an assumed "300-field limit" unless Microsoft documentation explicitly establishes that number as the applicable hard limit for your scenario.
The question for the community
This is where I want to turn the investigation into a discussion.
I've tested a table with Integer and Code fields and started exploring where the compiler warning appears.
But I still want to answer the bigger question:
After Business Central 29 moved table-extension fields into the same database table, what is the real practical field limit?
Is it primarily a field-count limitation?
Does the data type significantly change the result?
What happens when we add large Text fields?
What happens with BLOBs?
And at what point does the compiler warning appear compared with the actual SQL/database limit?
If you've already tested this in Business Central 29, I'd love to hear what you found.
FAQ
How many fields can a Business Central table have?
Microsoft's documented Business Central object specifications currently list 500 as the maximum number of fields in a record. However, the practical behavior around table-extension storage and the new BC 29 compiler warning deserves additional investigation.
What changed with table extensions in Business Central 29?
Microsoft introduced a new data model where fields from an AL table and its table extensions are stored in the same database table. This replaces the previous companion-table storage approach for this scenario.
Does Business Central 29 warn when a table has too many fields?
Yes. Business Central 29 introduces compiler warnings for tables and table extensions containing many normal fields that are approaching SQL extensibility limits.
Is 300 the Business Central table field limit?
Don't treat 300 as the definitive hard limit without qualification. The current Microsoft documentation lists 500 fields in a record, while the new compiler warning is an early warning about approaching SQL extensibility limits.
Does field data type affect the maximum?
This needs careful testing. SQL Server has both column-count and record-size constraints, and Business Central documents different storage characteristics for different field types. Testing only Integer or Code fields doesn't establish the behavior for every field type.
Conclusion
Business Central 29 changes more than just a compiler diagnostic.
The move to a new table-extension data model means fields from the base table and extensions are stored together, making SQL column and record-size limits more important when designing extensions.
The new compiler warning gives developers an early signal, but it also raises an interesting technical question: what is the real practical limit, and how does field type affect it?
That's what I'm exploring in this video.
If you have already tested the BC 29 table field limits, share your findings. Let's build a clearer picture of how this new data model behaves in real-world extension scenarios.
Subscribe to the channel for more Business Central development experiments, AL tips, and release-wave features, and connect with me on LinkedIn.
Comments
Post a Comment