Understanding the Difference Between Temporary Tables and SourceTableTemporary in Business Central

Summary

In Microsoft Dynamics 365 Business Central, performance and data handling are critical especially when dealing with intermediate calculations, staging data, or processing large datasets.

Developers often come across two commonly used approaches:

• a. Temporary Tables
• b. SourceTableTemporary property

At first glance, both seem to do the same thing: store data temporarily without writing to the database. But in reality, they serve different purposes and behave differently in real-world scenarios.

This blog explains:

1] What Temporary Tables are

2] What SourceTableTemporary is

3] Key differences between them

4] When to use which approach

5] Real-world development scenarios

Table of Contents

• 1. The Real Problem: Handling Temporary Data Efficiently
• 2. What are Temporary Tables?
• 3. What is SourceTableTemporary?
• 4. Key Differences
• 5. Practical Scenarios
• 6. Why Choosing the Right Approach Matters
• 7. Business Impact

The Real Problem: Handling Temporary Data Efficiently

Let’s take a real development scenario.

You are building a customization where:

• 1. Data needs to be fetched from multiple tables
• 2. Calculations need to be performed
• 3. Results should be displayed to the user
• 4. But no data should be stored permanently

Example Use Cases

1] Generating preview reports

2] Aggregating data before posting

3] Showing calculated insights on a page

4] Temporary staging before validation

The Challenge

If you use normal tables:

• a. Data gets stored permanently
• b. Cleanup becomes necessary
• c. Performance may degrade

If you misuse temporary structures:

• a. Data may not persist where needed
• b. UI may not behave correctly
• c. Logic becomes inconsistent

So the key question becomes:

Should you use a Temporary Table variable or SourceTableTemporary?

What are Temporary Tables?

Temporary tables are record variables that exist only in memory and are not stored in the SQL database.

Key Characteristics

• Defined using:

var
    TempSalesLine: Record “Sales Line” temporary;

• a. Data is stored in memory only
• b. Scope is limited to the variable
• c. Data is lost after execution ends

Behavior

• a. Fully controlled in AL code
• b. Can insert, modify, delete records
• c. No impact on actual database tables

Example

TempSalesLine.Init();
TempSalesLine.”Document No.” := ‘TEMP001’;
TempSalesLine.Insert();

This record exists only during runtime and never touches the database.

What is SourceTableTemporary?

SourceTableTemporary is a Page-level property.

It makes the entire page operate on a temporary version of its Source Table.

Definition

SourceTableTemporary = true;

Key Characteristics

• a. Applied at Page level, not variable level
• b. Entire dataset is temporary
• c. Page behaves like it is bound to a table but it is not persisted

Behavior

• a. Data must be manually populated (usually in OnOpenPage)
• b. Page UI works normally (sorting, filtering, editing)
• c. No database interaction happens

Example

trigger OnOpenPage()
begin
    Rec.Init();
    Rec.”No.” := ‘TEMP001’;
    Rec.Insert();
end;

Here, Rec is temporary because the page is set to SourceTableTemporary = true.

Key Differences

Practical Scenarios

Scenario 1: Data Processing Logic

You are calculating totals before posting a document.

Use Temporary Tables

Why?

• a. No UI required
• b. Full control in AL
• c. Lightweight and efficient

Scenario 2: Showing Preview Data on a Page

You want to show:

• a. Calculated results
• b. Filtered dataset
• c. Simulation before posting

Use SourceTableTemporary

Why?

• a. Works seamlessly with pages
• b. Supports UI interactions
• c. No database writes

Scenario 3: Hybrid Use Case

Sometimes you:

• a. Process data using Temporary Tables
• b. Then display it using a Page

Best Practice:

• a. Use Temporary Table for logic
• b. Pass data to a page with SourceTableTemporary = true

Why Choosing the Right Approach Matters

Using the wrong approach can lead to:

Business Impact

1. Improved Performance

Temporary data handling reduces database load and improves execution speed.

2. Cleaner Data Architecture

No unnecessary records stored → no cleanup jobs required.

3. Better User Experience

Users can preview and interact with data without affecting actual records.

4. Safer Development Practices

Avoids accidental data writes and improves system stability.

5. Flexible Customizations

Developers can build simulation, preview, and staging features easily.

6. Reduced Maintenance Effort

No need for background jobs to delete temporary records.

Final Thoughts

Both Temporary Tables and SourceTableTemporary are powerful tools—but they are not interchangeable.

Think of it like this:

• a. Temporary Table = Backend Processing Tool
• b. SourceTableTemporary = UI Representation Tool

Choosing the right one depends on where your logic lives:

• a. In code → Temporary Table
• b. On UI → SourceTableTemporary

I hope you found this blog useful!

“Discover How We’ve Enabled Businesses Like Yours – Explore Our Client Testimonials!” 

Please feel free to connect with us at transform@cloudfronts.com