Business Central 29: How Many Fields Can a Table Really Have?
Field limits for Business Central 29 tables and table extensions, tested in AL. Covers the compiler warning that starts after 293 fields (300 including platform fields), the SQL Server limits of 500 fields and 8060 bytes per record, and the version 29 change that stores extension fields in the same SQL table as the base table. A demo with decimal fields fails at publish time with a database command cancelled error at 470 and again at 469 fields.
- Introduction and context about field limits
- Overview of Business Central 29 changes
- Detect table approaching SQL column limit warning
- Table extension storage model change
- Investigating field limit numbers
- Microsoft documentation and SQL limitations
- Testing with field types and platform fields
- Publishing and data type implications
- Testing warning thresholds and field counts
- Companion table behavior and iterative testing
- Testing SQL limit and schema best practices
- Summary and conclusions
Overview
The video looks at how many fields a table can have in Business Central 29. In version 28 and earlier, a table extension created a separate companion table in SQL Server. From version 29, base table and extension fields are stored in the same SQL table, so the limits apply to the combined set of fields.
The presenter tests the new compiler warning for tables approaching the SQL column limit, then compares it with Microsoft documentation. He finds that the warning is a threshold, not the real limit. The real limits are 500 fields per record and 8060 bytes per record, and the data types you choose decide which one you hit first.
Key points
- From version 29, table extension fields are stored in the same SQL Server table as the base table fields. Up to version 28 a companion table was created, so field limits now apply to the combined fields.
- The compiler warning appears once a table has more than 293 fields, which with the 6 platform fields reports 300. The presenter says 300 is a warning threshold, not a documented maximum, and the build still compiles.
- In the demo the warning stopped showing at around 499 fields, and the build did not fail.
- SQL Server allows at most 500 fields per record and 8060 bytes per record. Record size can stop you before you reach 500 fields.
- A decimal takes 17 bytes and an integer takes 4. Variable-length text fields count 26 bytes each in the record.
- The presenter first estimated about 474 decimal fields, then recalculated 470 including platform fields and the primary key. Publishing failed with a database command cancelled error at 470 and again at 469, and he then tried 465 without showing the result.
- Business Central adds 6 platform fields to every table: system ID, created at, created by, modified at, modified by and row version. They count toward the limits.
Features
| Feature | Status | At |
|---|---|---|
| Detect table approaching SQL column limit warning | status not stated, demoed | 1:18 |
| New table extension data model | status not stated | 2:09 |
| 300 field warning threshold | status not stated, demoed | 8:16 |
| Platform system fields | status not stated, demoed | 9:42 |
| Record size byte limit | status not stated, demoed | 15:12 |
| Data type byte consumption | status not stated, demoed | 15:48 |
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 “SDH max field” at 8:28
- other “field 470” at 20:09
- other “field 469” at 21:05
- other “field 465” at 25:38
- other “row version” at 19:22
Not found in BC28-30: table “SDH max field”.
Quotes
- 2:09 “a new data model where fields from al table and its table extension are stored in same sql server table”
- 3:49 “Microsoft says that with new table extension data model all field from AL table are stored in the same database table and it makes”
- 4:44 “I have read some numbers 300 somebody said 400 somebody said 500. Now this is where I wanted to be careful. I’ve seen the”
- 4:44 “I don’t want to tell you that 300 is the definitive maximum limit because I haven’t found Microsoft documentation saying that 300 is the”
- 5:34 “the maximum number of records or fields in a record is 500. The same documentation also says about the record size.”
Disclaimers in the video
- 0:13 other: Business Central 29 changed how table extension fields are stored which we have discussed in an earlier video
- 24:26 subject-to-change: starting version 29, you’ll have to keep that in mind of thinking about the data type that you’re choosing
- 30:49 other: early warning necessary means that you should rethink about the fields that you are adding
- 32:13 other: I still need to understand uh what happens and at what point it kind of allows me to publish that extension
Presenters (as heard): Sad Dhani.