Study guide
6.1 Table Design & Extension
Microsoft Dynamics 365: Finance and Operations Apps Developer Associate MB-500
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Cloud & Ecosystem Architecture
1.1 Cloud vs On-Premises Architecture & Deployment Typologies 1.2 Microsoft Ecosystem Integration & Extensibility Boundaries 1.3 Lifecycle Services (LCS) & Implementation Portal
2 Chapter 2: ALM, Environments & Unified Developer Experience
2.1 Package Deployment & Servicing 2.2 Unified Developer Experience (UDE) & Power Platform Admin Center 2.3 Environment Management & Governance
3 Chapter 3: Visual Studio Development & Metadata
3.1 Visual Studio Extension Models & Packages 3.2 Application Explorer & Element Designers 3.3 Database Synchronization & Visual Studio Debugging
4 Chapter 4: Azure DevOps Version Control & CI/CD
4.1 Version Control Architecture: TFVC vs Git in F&O 4.2 Branching, Merging & Conflict Resolution 4.3 CI/CD Build Pipelines & Artifact Generation
5 Chapter 5: UI Elements: Forms, Menus & Labels
5.1 Form Design & Form Patterns 5.2 Menus, Menu Items & Navigation 5.3 Label Files & Localization
6 Chapter 6: Data Model: Tables & Relational Design
6.1 Table Design & Extension 6.2 Views, Queries & Maps 6.3 Data Entity Design & Modeling
7 Chapter 7: Classes, Attributes & AOT Modifiers
7.1 Class Architecture & Access Modifiers 7.2 Attributes & Reflection 7.3 Event Handlers & Pre/Post Method Events
8 Chapter 8: X++ Core Programming & CRUD Operations
8.1 X++ Syntax, Control Flow & Global Functions 8.2 Data Manipulation & CRUD Logic 8.3 Best Practice Rules & Compiler Diagnostics
9 Chapter 9: Advanced OOP, Chain of Command & Extensibility
9.1 OOP Principles & QueryBuilder Class 9.2 Chain of Command (CoC) Implementation 9.3 Extensibility Patterns & Non-Breaking Changes 9.4 Event Handler Classes & Delegate Architecture 9.5 Variable Scoping, Declaration Placement & Object Lifetime
10 Chapter 10: F&O Frameworks & SysTest Unit Testing
10.1 SysOperation Framework Architecture 10.2 Workflow Framework & SysExtensionSerializer 10.3 SysTest Framework & Test Explorer
11 Chapter 11: Reporting Architecture & SSRS Reports
11.1 Reporting Architecture & Datastores 11.2 Report Data Providers & Contracts 11.3 SSRS Report Design, Deployment & Security 11.4 Determining When to Use a Specific Reporting Tool or Platform
12 Chapter 12: Business Intelligence, Workspaces & Electronic Reporting
12.1 Electronic Reporting (ER) Framework 12.2 Power BI Integration & Analytical Workspaces 12.3 Workspaces, Tiles, Lists & KPIs 12.4 Excel Integration & Office App
13 Chapter 13: Integration Patterns & Service Endpoints
13.1 Integration Pattern Evaluation & API Selection 13.2 Custom Services & SOAP/REST Endpoints 13.3 Consuming External Services & REST/SOAP APIs
14 Chapter 14: Data Management Framework (DMF) & Batch Processing
14.1 DMF Concepts, Projects & Sequencing 14.2 Entity Change Tracking & Recurring Integrations 14.3 Composite Entities & Batch OData API 14.4 Monitoring Entity Status, Availability & Job Execution
15 Chapter 15: Power Platform, Business Events & Azure Integrations
15.1 Dual-Write & Virtual Entities 15.2 Business Events Framework 15.3 Azure Key Vault Integration & Secure Storage
16 Chapter 16: Role-Based Security & Extensible Data Security
16.1 Role-Based Security Architecture 16.2 Extensible Data Security (XDS) Policies 16.3 Security Enforcement & Best Practices
17 Chapter 17: Performance Optimization & Data Access Patterns
17.1 Table & Form Caching Mechanisms 17.2 Temporary Tables & Set-Based Operations 17.3 Query Tuning, Indexes & Concurrency 17.4 Diagnostics: Trace Parser, Async & Sandbox Frameworks 17.5 Tuning Variable Scope, Buffer Lifetime & Memory Pressure
6.1 Table Design & Extension
Key Takeaways Explain This Topic AI
Dynamics 365 Finance and Operations supports three distinct table types: Regular tables (persisted in Azure SQL Database), InMemory tables (cached in client/AOS memory and spilled to local disk beyond ~128 KB), and TempDB tables (physical temporary tables in SQL Server tempdb supporting joins and set-based operations).
Extended Data Types (EDTs) encapsulate primitive data types with centralized labels, help text, display formats, and array elements, while Base Enums define discrete integer-backed value sets that can be declared extensible (IsExtensible = True) or non-extensible.
When a Base Enum is marked as extensible (IsExtensible = True), the compiler generates internal hash IDs for enum values; X++ code must strictly compare enum values using symbol literals rather than hardcoded integers.
Table relations govern referential integrity and UI lookups, spanning Normal relations, Field Fixed relations (filtering source table records), Related Field Fixed relations (filtering target table records), and Surrogate Foreign Key relations linked via 64-bit RecId.
Table extensions permit non-intrusive additions—including custom fields, field groups, non-clustered indexes, and new relations—while enforcing strict immutability on base tables, prohibiting the deletion of standard fields, modification of existing data types, or alteration of ClusteredIndex and PrimaryIndex.
Last updated: September 2026
6.1 Table Design & Extension
Quick Answer: Tables form the foundational physical data layer in Dynamics 365 Finance and Operations (F&O). Tables are categorized into three types: Regular (physical tables in Azure SQL Database), InMemory (process memory temporary tables, spilling to disk files beyond 128 KB), and TempDB (physical temporary tables created inside SQL Server tempdb , supporting joins, transactions, and set-based operations). Data types are encapsulated through Extended Data Types (EDTs) and Base Enums . When Base Enums have IsExtensible = True , developers must never compare them against hardcoded integer values. Table relations enforce referential integrity across Normal , Field Fixed , Related Field Fixed , and Surrogate Foreign Key relationships. Through Table Extensions , developers can add new fields, field groups, non-clustered indexes, and relations to standard tables, but cannot delete standard fields, modify existing field data types, reduce string lengths, or alter the ClusteredIndex or PrimaryIndex .
1. Table Types & Persistence Architecture
In Dynamics 365 Finance and Operations, every table declared in the Application Object Tree (AOT) possesses a TableType property that dictates how the Application Object Server (AOS) and the underlying database engine manage storage, memory caching, indexing, and transactional boundaries.
Architectural Comparison of Table Types
Architectural Dimension Regular Table InMemory Table TempDB Table
Physical Storage Location Azure SQL Database (clustered column/index on disk) Client or AOS process memory; spills to disk temporary files beyond ~128 KB SQL Server tempdb database as a physical temporary table ( t12345_... )
Scope & Lifetime Permanent persistence until explicitly deleted by business logic In-memory buffer variable scope; dropped when record variable goes out of scope Current user/batch AOS session; dropped when table buffer variable loses scope
Direct SQL Joins with Regular Tables Fully supported across all standard SELECT queries Unsupported; joining InMemory with Regular pulls Regular rows to AOS memory Fully supported; executes natively inside SQL Server query optimizer
Set-Based SQL Operations insert_recordset , update_recordset , delete_from fully supported Executed row-by-row in AOS memory; no SQL set-based acceleration Fully supported ( insert_recordset , update_recordset , delete_from )
Transaction (TTS) Rollback Support Fully rolled back upon ttsabort or unhandled exceptions No rollback; memory mutations persist even if transaction aborts Fully rolled back; participates in standard SQL transaction log rollbacks
Foreign Keys & Secondary Indexes PrimaryIndex, AlternateKey, NonClustered indexes, Foreign Keys Non-clustered indexes supported in memory; no foreign key constraints Non-clustered indexes supported in tempdb ; no persistent foreign keys
Typical Enterprise Use Case Master data, transactional documents, general ledger journals Lightweight UI dialog dropdowns, legacy reporting parameter caches High-volume staging, complex multi-table data processing, SSRS RDP processing
Technical Deep Dive: TempDB vs. InMemory
Prior to Dynamics AX 2012, temporary tables operated strictly in memory (the InMemory type). When an InMemory table grows beyond approximately 128 KB, the runtime writes temporary binary files ( .tmp ) to the local disk of the executing tier (AOS or client). This disk thrashing degrades performance for large datasets. Furthermore, queries that join an InMemory table with a Regular database table cannot execute set-based SQL joins; instead, the AOS issues queries to retrieve candidate records from the SQL database and evaluates joins row-by-row in application memory.
In contrast, TempDB tables instantiate a physical temporary table inside the database server's dedicated tempdb catalog. Because the table exists inside SQL Server:
The SQL query optimizer can join TempDB tables with standard transactional tables (such as CustTable or SalesLine ) using hash joins, nested loops, or merge joins.
Set-based operations ( insert_recordset ) execute entirely within the database engine without pushing record buffers across the network to the AOS tier.
TempDB tables participate in ttsbegin and ttcommit transactional scopes. If an exception triggers ttsabort , operations performed on the TempDB table are rolled back in the SQL transaction log. // Instantiating and utilizing a TempDB table for set-based processing
- TmpAccountSum tmpAccountSum;
- ttsbegin;
- // Perform set-based bulk insert directly in SQL tempdb
- insert_recordset tmpAccountSum (AccountNum, BalanceAmount)
- select AccountNum, sum(AmountMST) from custTrans
- group by AccountNum;
- // Update or join directly with Regular tables
- CustTable custTable;
- while select tmpAccountSum
- join custTable
- where custTable.AccountNum == tmpAccountSum.AccountNum
- {
- // High-performance joined cursor
- }
- ttscommit;
- 2. Fields, Extended Data Types (EDTs) & Base Enums
Dynamics 365 F&O enforces strong data typing and metadata-driven encapsulation. Rather than binding database columns directly to primitive SQL primitives (such as VARCHAR(20) or DECIMAL(18,2) ), the application abstracts data through Extended Data Types (EDTs) and Base Enums .
Extended Data Types (EDTs)
An EDT is an encapsulation of a primitive type (String, Integer, Int64, Real, Date, UtcDateTime, Guid) that enriches the primitive with business semantics:
Inheritance ( Extends Property): EDTs support single inheritance. For example, CustAccount extends AccountNum , which in turn extends SysGroup . A sub-type inherits the label, help text, display length, string size, and formatting of its ancestor unless explicitly overridden.
Encapsulated Metadata: An EDT defines user-interface properties including Label , HelpText , DisplayLength , StringSize , NoOfDecimals , and alignment.
EDT Relations (Legacy vs. Modern): In legacy versions (AX 2012 and earlier), foreign key relationships could be defined directly on EDTs. In modern Dynamics 365 Finance and Operations, EDT relations are deprecated and obsolete . All relational constraints and lookups must be defined on table-level relations.
Base Enums: Extensible vs. Non-Extensible
A Base Enum is a collection of named constant values backed by an underlying 32-bit integer in the SQL database.
Non-Extensible Base Enums ( IsExtensible = False ):
The enum definition is closed. Developers cannot add new enum values via extensions.
Enum values have deterministic, sequential integer assignments defined directly in metadata (e.g., Value0 = 0 , Value1 = 1 , Value2 = 2 ).
Extensible Base Enums ( IsExtensible = True ):
Developers can create an Enum Extension in a separate package and append custom enum values.
The Hash ID Mechanism: Because multiple independent extensions (from different ISVs or partners) might add enum values, Microsoft eliminated static integer numbering for extensible enums. When an extensible enum compiles, the build system generates an internal 32-bit hash value for each extended element.
Warning
Critical Exam Rule: Never Compare Extensible Enums to Hardcoded Integers In X++, writing if (salesTable.SalesStatus == 1) is a severe anti-pattern that fails or causes unpredictable bugs when dealing with extensible enums. Because extended enum elements receive non-sequential system-generated integer IDs, developers must always use the symbolic enum literal: if (salesTable.SalesStatus == SalesStatus::Backorder) .
3. Field Groups & UI Automation
Field groups organize table columns into functional collections that simplify form design, report generation, and data entity mapping.
System Field Groups
AutoReport : Specifies the default columns included when a user generates an ad-hoc auto-report from a grid.
AutoLookup : Governs the automatic dropdown lookup grid rendered when a user clicks a lookup control on a form, provided no custom lookup form or lookup method overrides the control. Columns placed in AutoLookup appear sequentially in the lookup grid.
AutoBrowse : Used by internal navigation tools to identify primary browse columns.
Custom Field Groups and Dynamic Extension Propagation
When designing forms, developers drag a table's Field Group onto a form design pattern (such as a FastTab or Details group) rather than binding individual fields. When a developer subsequently extends the table in a separate model and adds custom fields to that Field Group, the new fields automatically propagate to all forms and data entities consuming that Field Group without requiring any modification to the form extension metadata.
4. Index Architecture & Clustered vs. Non-Clustered Indexes
Indexes accelerate data retrieval, enforce business uniqueness, and dictate physical disk layout in Azure SQL Database.
Index Types and Properties
PrimaryIndex : The unique index designated as the primary identifier of table rows. It is referenced by foreign keys and table relation definitions.
AlternateKey : A unique index ( AllowDuplicates = No ) representing the natural business key of the record (for example, AccountNum on CustTable or ItemId on InventTable ). When a table uses a Surrogate Key architecture, the AlternateKey serves as the human-readable replacement key.
ClusteredIndex : Dictates the physical sort order of rows stored on SQL Server data pages. In Dynamics 365 F&O, the ClusteredIndex defaults to the surrogate key RecId ( RecIdIndex ). Microsoft strongly advises against modifying the clustered index of standard tables because altering disk layout across multi-million row transactional tables degrades high-concurrency inserts.
NonClustered Indexes: Secondary search structures containing index key columns and row locators pointing to the clustered index. Can be unique ( AllowDuplicates = No ) or non-unique ( AllowDuplicates = Yes ).
Covering Indexes with IncludedColumnList
To eliminate expensive SQL Server bookmark lookups (Key Lookups) during read operations, developers configure covering indexes. By specifying columns in the index's IncludedColumnList property, the database engine stores non-key payload data directly at the leaf level of the B-tree index without incorporating those columns into the index key itself. This keeps the index key narrow and compact while satisfying SELECT queries entirely from the index.
5. Table Relations & Referential Integrity
Table relations establish logical connections between tables, enforce referential integrity constraints, and drive automatic lookup behaviors.
- Classification of Table Relations Table Relations Architecture
- ├── Normal Relation (Source.Field == Target.Field)
- ├── Field Fixed Relation (Source.Type == FixedConstant)
- ├── Related Field Fixed Rel (Target.Type == FixedConstant)
- └── Foreign Key Relation (Surrogate RecId -> Target AlternateKey)
Normal Relation:
A standard equi-join matching a field in the source table to a field in the target table.
Example: CustTrans.AccountNum == CustTable.AccountNum .
Field Fixed Relation:
Filters records based on a fixed integer or enum condition applied to the source table .
Example: On a polymorphic credit table, CreditTable.AccountType == AccountType::Customer restricts the relation so that foreign keys only validate when the current record represents a customer.
Related Field Fixed Relation:
Filters records based on a fixed integer or enum condition applied to the target (related) table .
Example: In document handling ( DocuRef ), the relation to CustTable specifies DocuRef.RefTableId == tableNum(CustTable) . The relation only links to target records where the target table identifier matches CustTable .
Surrogate Foreign Key Relations:
Relates a 64-bit integer ( RecId ) foreign key field in the child table to the RecId primary key of the parent table.
Bound to an Alternate Key on the parent table. At runtime, the client framework utilizes the parent table's ReplacementFieldGroup to display human-readable business values (such as AccountNum ) in UI controls while storing the surrogate RecId in the database.
6. Table Extensions & Immutability Boundaries
In Dynamics 365 Finance and Operations, over-layering is deprecated. All customizations must be implemented via Extensions . When extending a standard table (e.g., creating CustTable.Extension ), developers must understand what properties are modifiable versus which base properties are strictly immutable.
Extension Capabilities vs. Immutability Rules
Action / Property Extensible? Implementation Rules & System Behavior
Add Custom Fields Yes Prefix field names with custom model prefix (e.g., ABC_TrackingCode ) to prevent naming collisions.
Add Custom Field Groups Yes Custom field groups can be created; standard or custom fields can be placed inside them.
Modify Existing Field Groups Yes Developers can append custom fields or standard unassigned fields to existing standard field groups.
Add New Indexes Yes Developers can add new non-clustered indexes ( AllowDuplicates = Yes or No ).
Add New Table Relations Yes Custom Normal, Field Fixed, and Foreign Key relations can be added to standard tables.
Delete Standard Fields NO Immutable. Standard fields cannot be deleted or hidden at the table metadata level.
Modify Existing Field Types NO Immutable. You cannot change a standard field from String to Integer, or swap its underlying EDT.
Reduce Base Field String Size NO Immutable. You cannot decrease the length of a standard string field (would truncate standard data).
Change TableType Property NO Immutable. You cannot convert a Regular table to TempDB or InMemory via extension.
Modify Standard Indexes NO Immutable. You cannot delete standard indexes, remove fields from them, or change their uniqueness.
Change Primary or Clustered Index NO Immutable. The base table's PrimaryIndex and ClusteredIndex properties cannot be changed.
Enable Tracking Properties Yes You can switch CreatedDateTime , ModifiedDateTime , CreatedBy , ModifiedBy from No to Yes .
- 7. Scenario Walk-Through: Extending CustTable with Custom Logistics Tracking
- Scenario Description
An enterprise shipping company requires adding a custom carrier tracking account ( ABC_CarrierAccountNum ) and an automated delivery confirmation requirement flag ( ABC_RequireSignature ) to the standard CustTable . In addition, queries filtering by carrier account must execute with sub-second response times, and the fields must automatically render on customer lookup forms.
Step-by-Step Implementation Flow
Create Extended Data Types:
In Visual Studio, create EDT ABC_CarrierAccountNum extending AccountNum with Label "Carrier Account Number".
Create EDT ABC_RequireSignature extending NoYesId with Label "Require Delivery Signature".
Create Table Extension ( CustTable.MyModelExtension ):
Right-click CustTable in the AOT and select Create extension .
Under the Fields node, drag ABC_CarrierAccountNum and ABC_RequireSignature .
Enable Database Tracking Flags:
In the extension properties, verify that ModifiedDateTime and ModifiedBy are set to Yes to audit changes.
Add Field to Standard Field Group:
Expand Field Groups > AutoLookup and drag ABC_CarrierAccountNum into the group. This ensures the carrier account appears in standard customer dropdowns.
Create Covering Non-Clustered Index:
Expand Indexes , create index ABC_CarrierAccountIdx with AllowDuplicates = Yes .
Add ABC_CarrierAccountNum and DataAreaId to the index keys.
Add ABC_RequireSignature to IncludedColumnList . This creates a covering index that resolves carrier verification queries without triggering SQL Server bookmark lookups.
Build and Synchronize:
Compile the project and perform a full database synchronization to generate the schema additions in Azure SQL Database.
8. Real-World Exam Traps: Table Design & Extensions
Warning
Exam Trap 1: Attempting to Join InMemory Tables with Regular Tables in SQL A common question presents a query joining a temporary table to SalesLine and asks why performance is catastrophic or why an error occurs. If the temporary table is InMemory , SQL Server cannot join it directly with regular tables; the AOS executes row-by-row lookups. The correct architectural remedy is converting the table to TempDB .
Warning
Exam Trap 2: Hardcoding Integer Values on Extensible Base Enums Questions frequently ask which code snippet is correct when checking an extensible enum. Options containing if (myTable.Status == 2) are traps. The compiler assigns hash-based integer IDs to extensible enum values. The only correct approach is referencing the symbolic enum literal: if (myTable.Status == MyEnum::Approved) .
Warning
Exam Trap 3: Confusing Field Fixed with Related Field Fixed Relations Exam candidates frequently invert these two relation types. Remember the rule: Field Fixed filters the current (source) table buffer, whereas Related Field Fixed filters the foreign (target) table buffer. In polymorphic document references like DocuRef , matching DocuRef.RefTableId == tableNum(CustTable) is a Related Field Fixed relation from the perspective of tables pointing to DocuRef.
Warning
Exam Trap 4: Believing Base Table Indexes Can Be Modified in an Extension When an existing index on a standard table causes performance bottlenecks, an exam question may propose modifying the standard index via an extension (e.g., adding an included column or changing AllowDuplicates ). This is impossible because standard indexes are immutable. The valid solution is creating a new non-clustered index in the table extension.