How can AL developers use temporary tables to improve data processing and avoid unnecessary database writes?

Temporary tables are an important Business Central development technique when data needs to be processed, transformed, aggregated or staged without permanently storing every intermediate record in the database.

Microsoft describes a temporary table as a temporary variable that holds table data in memory rather than storing it in the database. Operations on temporary tables are performed on the Business Central Server, which can reduce network and SQL database interaction.

The basic idea

Suppose an application needs to process 50,000 records.

A poor design might repeatedly:

Read database
   ↓
Modify database
   ↓
Read database
   ↓
Modify database
   ↓
Read database
   ↓
Modify database

If the intermediate data does not actually need to be persisted, those database writes may be unnecessary.

A better design can be:

Database
   ↓
Read required data
   ↓
Temporary Table
   ↓
Process / Calculate / Aggregate
   ↓
Final Result
   ↓
Write only required persistent records

Why temporary tables can improve performance

Temporary data isn't stored in the database, and Microsoft notes that interactions with temporary tables occur on the Business Central Server. This can reduce load on both the network and SQL database server.

Imagine a reporting process that calculates:

Customer
   ↓
Invoices
   ↓
Payments
   ↓
Outstanding Amount
   ↓
Customer Risk

There may be no reason to create permanent database rows simply to hold the intermediate calculations.

A temporary table can act as a processing buffer.

Three common ways to implement temporary tables

Business Central supports several approaches, including:

• TableType = Temporary
• A temporary record variable
• SourceTableTemporary = true on a page

Microsoft documents these as supported approaches, with different design implications.

For example:

var
    TempCustomer: Record Customer temporary;

The record variable behaves like a table buffer, but its data is temporary.

Another approach is:

table 50100 "Customer Processing Buffer"
{
    TableType = Temporary;

    fields
    {
        field(1; "Customer No."; Code[20]) { }
        field(2; "Balance"; Decimal) { }
    }
}

The important concept is not the syntax.

The important question is:

Does this data represent business state that must survive, or is it only intermediate processing data?

Temporary tables in reports

Temporary tables are particularly useful when report data is calculated from multiple sources or doesn't map directly to a single database field.

For example:

Customer Ledger
      +
Sales Invoice
      +
Payment Data
      ↓
Business Calculation
      ↓
Temporary Report Dataset
      ↓
Report

Microsoft specifically documents temporary tables as useful for report datasets where values are produced by calculations or processing rather than directly mapped database fields.

Temporary does not mean unlimited

This is an important enterprise consideration.

A temporary table can reduce database writes, but it does not magically make an inefficient algorithm efficient.

For example, this can still be problematic:

FOR EACH Customer
    FOR EACH Invoice
        FOR EACH Payment
            Calculate...

If the algorithm itself is inefficient, moving the intermediate result into memory doesn't automatically solve the underlying problem.

Developers still need to consider:

• Number of records
• Memory consumption
• Filtering
• Indexes on source tables
• Query design
• Aggregation strategy
• Record locking
• Processing duration

Security consideration

Temporary tables have an interesting security behaviour: because the data isn't read from or written to the database, the normal permission system does not apply to operations on the temporary data itself. Microsoft explicitly documents this behaviour.

That means developers should think carefully about where the source data came from and what data is being exposed, especially in pages, reports and integrations.

Practical scenario

Imagine a Business Central report that calculates customer profitability.

Instead of repeatedly updating a permanent "Profitability Calculation" table:

Sales Invoice
      ↓
Calculate
      ↓
Temporary Profitability Buffer
      ↓
Display Report

Only persistent business data remains in the database.

The professional rule

Use a temporary table when:

The data is required for processing or presentation but does not itself represent persistent business state.

Do not use temporary tables simply because they appear faster.

The correct design depends on the purpose and lifecycle of the data.