Study guide
4.1: Building Tables, Fields, Keys & Data Types
Microsoft Dynamics 365 Business Central Developer MB-820
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Business Central Architecture & Platform Foundation
1.1 Business Central Architecture & Extensions Model 1.2 Customization Lifecycle: Cloud (SaaS) vs. On-Premises 1.3 Base Application, System Application & AppSource
2 Chapter 2: Development Environment & Configuration
2.1: Development Environment Setup: VS Code, AL: Go! Projects & Sandboxes 2.2: AL Configuration Files: app.json & launch.json Parameters 2.3: Multi-Root Workspaces & Project Dependencies
3 Chapter 3: Extension Lifecycle: Debugging, Deployment, Multilanguage & Maintenance
3.1 Debugging & Snapshot Debugging in Visual Studio Code 3.2 Extension Deployment & Schema Synchronization Modes 3.3 Multilanguage Development with XLIFF & Translation Files 3.4 Extension Maintenance, Deprecation & Breaking Changes
4 Chapter 4: Tables, Table Extensions & Data Modeling
4.1: Building Tables, Fields, Keys & Data Types 4.2: Table Extensions & Table Modifications 4.3: FlowFields, FlowFilters & Table Relations 4.4: Enums, Enum Extensions & Extensible Types
5 Chapter 5: Pages, Page Extensions & Role Centers
5.1: Page Types, Anatomy & Control Structure 5.2: Building Card, List & Document Pages 5.3: Page Extensions & Page Customizations 5.4: Role Centers, Headlines & Cue Data Modeling
6 Chapter 6: Reports & Document Generation
6.1 Report Architecture, DataItems & Dataset Design 6.2 Report Layouts: RDLC, Word, Excel & Multilanguage 6.3 Document Reports & Request Page Programming 6.4 Processing-Only Reports & Report Substitutions
7 Chapter 7: XMLports & Data Exchange
7.1 XMLport Architecture, Formats & Properties 7.2 XMLport Node Hierarchy, Triggers & Stream Manipulation
8 Chapter 8: Codeunits, Events, Triggers & Interfaces
8.1 Codeunit Types, Execution & SingleInstance Codeunits 8.2 Events, Publishers, Subscribers & Event Isolation 8.3 Interfaces, Polymorphism & Dynamic Codeunit Invocation 8.4 Installation & Upgrade Codeunits, Data Transfer & Archive
9 Chapter 9: Entitlements, Permissions & Queries
9.1 Permission Sets & Permission Set Extensions 9.2 Inherent Permissions, Inherent Entitlements & Security Scoping 9.3 Query Objects: Joins, Aggregation & Query as Report Data Source
10 Chapter 10: UI Customization, User Assistance & Onboarding
10.1 Profile Objects & Custom Page Views in AL 10.2 User Assistance: ToolTips, Teaching Tips & In-App Tours 10.3 Assisted Setup, Guided Experience & Onboarding Checklists
11 Chapter 11: Business Central Design Patterns & Data Process Model
11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns 11.2 Document Standards: Header, Line, History & Journal Architectures 11.3 Data Process Model & Posting Routine Architecture
12 Chapter 12: AL Language Fundamentals, Data Types & Variables
12.1 AL Syntax, Variable Scope, Primitive & Complex Data Types 12.2 Expressions, Control Flow Statements & Built-in Functions 12.3 Access Modifiers (Public, Internal, Local) & Code Scoping
13 Chapter 13: Data Manipulation, Streams & Error Handling
13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions 13.2 Streams, File Management & TempBlob 13.3 Error Handling: ErrorInfo, TryFunctions & Collectible Errors
14 Chapter 14: Testing, Test Toolkit & Automation
14.1 Testing Architecture & the Test Toolkit 14.2 Authoring Test Codeunits & UI Handler Functions
15 Chapter 15: Telemetry, Performance Optimization & Analyzers
15.1 Telemetry Configuration & Azure Application Insights 15.2 Custom Telemetry Signals with Session.LogMessage & KQL 15.3 Performance Profiler, SetLoadFields & Query Optimization 15.4 AL Analyzers: CodeCop, AppSourceCop, UICop & PTECop
16 Chapter 16: REST Web Services & JSON in AL
16.1 HttpClient, HttpRequestMessage & REST API Consumption 16.2 JSON Processing: JsonObject, JsonArray, JsonValue & JsonToken
17 Chapter 17: API Pages, OData, Read Scale-Out & OAuth
17.1 API Pages (PageType=API), OData Web Services & REST Endpoints 17.2 Bound and Unbound OData Actions with [ServiceEnabled] 17.3 Read Scale-Out Architecture & DataAccessIntent Configuration 17.4 Microsoft Entra ID App Registrations & OAuth 2.0 Integration
4.1: Building Tables, Fields, Keys & Data Types
Key Takeaways Explain This Topic AI
Tables in AL define physical SQL Server schema and business logic triggers; every table requires a unique primary key that is clustered by default.
DataClassification is mandatory for every table and field to maintain GDPR compliance and data governance (e.g., CustomerContent, EndUserIdentifiableInformation).
AL distinguishes between Text (case-preserving, dynamic) and Code (auto-uppercasing, trimmed, optimized for identifiers and primary keys).
IncludedFields on secondary keys creates SQL covering indexes that optimize query retrieval performance without inflating the index tree size.
The Rec.Insert(true) parameter controls execution of the OnInsert trigger, whereas underlying platform database events fire unconditionally.
Last updated: August 2026
4.1 Building Tables, Fields, Keys & Data Types
In Microsoft Dynamics 365 Business Central, tables represent the structural foundation of the application data model. Every business entity—from Master Data (such as Customers and Items) to Transactional Headers and Lines (such as Sales Headers and Sales Lines)—is declared as a strongly typed AL table object. Mastering table architecture, data types, indexing mechanics, and trigger lifecycles is a fundamental prerequisite for passing the MB-820 certification exam.
1. Table Object Architecture & Physical Storage
When an AL table object is compiled and published in an extension, the Business Central Server (runtime service tier) maps the table definition directly to a physical Microsoft SQL Server (or Azure SQL Database) table.
- Table Declaration Syntax table 50100 "Reward Level"
- {
- DataClassification = CustomerContent;
- Caption = 'Reward Level';
- LookupPageId = "Reward Level List";
- DrillDownPageId = "Reward Level List";
- DataPerCompany = true;
- fields
- {
- field(1; "Level Code"; Code[20])
- {
- Caption = 'Level Code';
- DataClassification = CustomerContent;
- NotBlank = true;
- }
- field(2; "Minimum Points"; Decimal)
- {
- Caption = 'Minimum Points';
- DataClassification = CustomerContent;
- DecimalPlaces = 0 : 2;
- MinValue = 0;
- }
- field(3; "Discount Percentage"; Decimal)
- {
- Caption = 'Discount Percentage';
- DataClassification = CustomerContent;
- MinValue = 0;
- MaxValue = 100;
- DecimalPlaces = 0 : 5;
- }
- field(4; "Active"; Boolean)
- {
- Caption = 'Active';
- DataClassification = CustomerContent;
- InitValue = true;
- }
- }
- keys
- {
- key(PK; "Level Code")
- {
- Clustered = true;
- }
- key(PointsKey; "Minimum Points", "Active")
- {
- IncludedFields = "Discount Percentage";
- }
- }
- trigger OnInsert()
- begin
- TestField("Level Code");
- end;
- trigger OnModify()
- begin
- end;
- trigger OnDelete()
- begin
- end;
- trigger OnRename()
- begin
- end;
- }
- Table Types in AL
The TableType property specifies the physical behavior and storage mechanism of the table object:
TableType Description & Storage Location Exam Use Case
Normal (Default) Physical SQL Server table created in the tenant database. Stores persistent data partitioned per company (or across companies if DataPerCompany = false ). Master data, journals, ledger entries, setup tables.
Temporary In-memory table structure defined at schema level. Data exists only during the client session or AL execution scope. Buffer calculations, intermediate reporting datasets, wizard UI state.
CRM Virtual integration table mapped to Microsoft Dataverse (Common Data Service) entities. Synchronizing Dynamics 365 Sales/Dataverse records with Business Central.
ExternalSQL Connects directly to external SQL databases using external connection strings. Legacy system integrations without staging data inside Business Central.
Exchange / MicrosoftGraph Virtual integration tables for Exchange mailboxes or Graph API resources. Office 365 booking and email synchronization.
Exam Watch: When a table is declared with TableType = Temporary , all instances of that table across the entire AL project behave as temporary records without writing to SQL. However, if a standard table ( TableType = Normal ) is declared as a variable in AL code, you can make that specific variable temporary using the Temporary keyword: var TempCustomer: Record Customer temporary; .
Loading diagram... AL Table Architecture and SQL Physical Mapping
2. Field Data Types, ID Ranges & Properties
Fields define the data columns of a table. In Business Central, field numbers determine licensing boundaries, schema stability, and extension namespaces.
Field Number Ranges
1 to 49,999: Base Application and Microsoft system fields.
50,000 to 99,999: Per-Tenant Extensions (PTE) created for specific customer environments.
1,000,000 to 69,999,999: ISV / AppSource publisher ranges allocated by Microsoft Partner Center.
2,000,000,000 to 2,147,483,647: System fields automatically generated by the platform (e.g., SystemId , SystemCreatedAt , SystemCreatedBy , SystemModifiedAt , SystemModifiedBy , SystemRowVersion ).
Core AL Data Types
Data Type SQL Representation Characteristics & Best Practices
Code[N] NVARCHAR(N) Converts all letters to uppercase automatically. Trims leading and trailing spaces. Used for primary keys, identifier codes, document numbers, and foreign key references. Maximum length is 2048 characters.
Text[N] NVARCHAR(N) Preserves casing and whitespace. Used for descriptions, names, addresses, and free-text notes. Unbounded Text (without [N] ) maps to NVARCHAR(MAX) .
Integer INT 32-bit signed integer (-2,147,483,648 to 2,147,483,647). Used for line numbers, quantities in discrete units, and counters.
BigInteger BIGINT 64-bit signed integer. Used for high-volume sequence numbers and transaction identifiers.
Decimal DECIMAL(38,20) High-precision floating-point number. Standard for amounts, unit prices, discounts, and exchange rates. Controlled by the DecimalPlaces property.
Boolean TINYINT true or false . Stored in SQL as 1 or 0.
Date DATETIME Stores calendar dates (e.g., 2026-08-29D ). Special values include 0D (blank/empty date) and ClosingDate (for year-end closing entries).
Time DATETIME Stores time of day with millisecond precision (e.g., 143000T or 0T ).
DateTime DATETIME Coordinated Universal Time (UTC) timestamp. Combines date and time into a single point in time.
Duration BIGINT Elapsed time in milliseconds. Can be added or subtracted from DateTime variables.
DateFormula NVARCHAR(32) Dynamic date calculation formula (e.g., 1M+10D , CM+15D , -1Y ). Evaluated using the CalcDate() function.
Guid UNIQUEIDENTIFIER 128-bit globally unique identifier. Used for system entity references and web API keys.
Blob VARBINARY(MAX) Binary Large Object. Used for storing arbitrary byte arrays, small files, or legacy bitmap images. Manipulated via AL InStream and OutStream .
Media / MediaSet Special Storage Cloud-optimized media storage for images, photos, and document attachments. Managed via the Azure Blob media store rather than inflating SQL data rows.
RecordId Binary reference Encodes the table ID and primary key values of any arbitrary record in the database.
Essential Field Properties
DataClassification: Mandatory for every field under modern AL rules. Options include:
CustomerContent : Data entered by users during business operations (e.g., customer names, transaction amounts).
EndUserIdentifiableInformation (EUII): Personally Identifiable Information (PII) such as email addresses, home addresses, phone numbers, or national IDs.
AccountData : Billing, bank account, and payment information.
EndUserPseudonymousIdentifiers (EUPI): User IDs, GUIDs, or telemetry identifiers.
SystemMetadata : System-generated configuration and technical metadata.
OrganizationIdentifiableInformation (OII): Public company numbers, VAT registration numbers.
ToBeClassified : Temporary placeholder during initial development; rejected for AppSource by AS0016 ( Fields of field class 'Normal' must use the DataClassification property and its value should be different from ToBeClassified ).
ExtendedDataType: Enhances UI presentation and client behavior:
PhoneNo : Formats as telephone link and triggers telephony integration on mobile devices.
URL : Renders as clickable hyperlink in the web client.
Email : Renders as clickable mailto: link.
Barcode : Formats with barcode font.
Masked : Obscures characters on UI input (for passwords/tokens).
Ratio : Displays a graphical progress bar or ratio indicator.
Validation & Numeric Constraints:
NotBlank = true : Prevents the user from entering an empty value in UI pages.
MinValue / MaxValue : Sets boundary validation limits at the data layer.
DecimalPlaces = 0:5 : Sets minimum and maximum decimal precision displayed on pages and stored in database operations.
AutoIncrement = true : Generates automatically increasing 32-bit/64-bit integer values on record insertion. Only allowed on primary key fields of type Integer or BigInteger .
3. Keys, Indexes & SIFT Architecture
Keys define the ordering and indexing of table records in SQL Server. AL supports primary keys, secondary keys, covering indexes, and SIFT (SumIndexFields Technology) indexes.
Primary Key (PK)
Every table must have exactly one primary key defined as the first key in the keys block.
The primary key enforces row uniqueness.
In Business Central, the primary key is Clustered = true by default. This physically arranges the table rows on SQL Server data pages in ascending order of the primary key fields.
Primary key fields cannot be modified or dropped in table extensions.
Secondary Keys & SQL Index Optimization
Secondary keys create non-clustered indexes on the SQL Server table to accelerate search, filtering, and sorting operations. key(CustSearchKey; "Country/Region Code", "City", "Name")
- {
- Unique = false;
- IncludedFields = "Balance (LCY)", "Contact";
- }
- Key Properties Breakdown
Unique: When set to true , the SQL Server engine enforces a unique constraint on the key columns, preventing duplicate combinations across records.
IncludedFields (Covering Index): Adds specified non-key fields to the leaf level of the non-clustered index tree. When a query filters by Country/Region Code and selects Contact , SQL Server fulfills the entire query from the index pages without performing an expensive clustered index key lookup (bookmark lookup) against the physical table data page.
SumIndexFields: Designates numeric fields ( Decimal , Integer , BigInteger ) for real-time aggregate calculation via SIFT (SumIndexFields Technology). SQL Server maintains indexed views for these aggregates, enabling instantaneous calculations of totals across millions of records.
MaintainSQLIndex / MaintainSIFTIndex: Setting either property to false disables the creation of physical SQL Server index structures, saving write performance when the key is used solely for AL in-memory sorting.
4. Table Triggers, Field Triggers & Validation Order
Business Central enforces business rules through a structured event and trigger execution hierarchy.
- Table-Level Triggers trigger OnInsert()
- begin
- // Executed before a new record is committed to the database
- // Rec represents the record being inserted
- end;
- trigger OnModify()
- begin
- // Executed before an existing record is updated in the database
- // xRec contains the record state before modification
- end;
- trigger OnDelete()
- begin
- // Executed before a record is removed
- // Used to verify referential integrity and delete child lines
- end;
- trigger OnRename()
- begin
- // Executed when primary key fields are modified
- // xRec holds the OLD primary key, Rec holds the NEW primary key
- end;
- The RunTrigger Parameter in AL Data Operations
When inserting, modifying, or deleting records in AL code, the developer explicitly controls trigger execution: // Trigger bypassed: OnInsert trigger does NOT run
RewardLevel.Insert();
- // Trigger executed: OnInsert trigger runs
- RewardLevel.Insert(true);
- // Direct field assignment: OnValidate does NOT run
- RewardLevel."Minimum Points" := 500;
- // Field validation: OnValidate trigger runs
- RewardLevel.Validate("Minimum Points", 500);
Exam Trap: Rec.Insert(false) bypasses the AL OnInsert trigger written inside the table object. However, platform-level telemetry events, SQL constraints, and event subscribers bound to OnDatabaseInsert continue to fire at the server tier. Always use .Validate() and Insert(true) when creating business transactions to prevent data corruption.