AL Table Cleanup: Choosing Between Truncate and DeleteAll

If you’ve ever had to clear out a massive table in AL, you know how slow and painful it can be. The usual suspect DeleteAll does the job, but it’s not exactly built for speed when you’re dealing with hundreds of thousands of rows.

Rec.Truncate, a new method introduced in Business Central 2025 release wave 2 (BC27). It’s designed specifically for high-volume deletions and gives you a much faster, cleaner way to wipe out data when most of the table needs to go.

What Rec.Truncate Actually Does

The method looks like this:

[Ok := ] Record.Truncate([ResetAutoIncrement: Boolean])

It returns false if truncation isn’t supported, and you can choose whether to reset AutoIncrement fields to 0 or keep their current values. That flexibility makes it useful for both test resets and production cleanup.

Why You Should Care

Rec.Truncate is built for speed. Instead of deleting rows one by one and logging each operation, it performs a bulk delete that skips all that overhead. It’s perfect for:

• Clearing out log, staging, or batch tables
• Resetting data between test cycles
• Archiving scenarios where you only want to keep a small subset of rows

It complements DeleteAll rather than replacing it. Use DeleteAll when you need to honor filters or trigger logic. Use Truncate when you just want the data gone—fast.

How It Works Behind the Scenes

When you call Rec.Truncate without filters, it simply wipes the table using SQL’s truncate behavior. If you do use filters, it gets a bit more clever: rows you want to keep are copied to a temp table, the base table is truncated, and then the kept rows are copied back.

This avoids row-level logging and speeds things up dramatically. The platform also temporarily drops and re-enables views and indexes to make the operation even faster.

One important note: it doesn’t run OnBeforeDelete or OnAfterDelete triggers. If those exist, the truncate will fail.

Limitations to Watch Out For

Truncate is powerful, but it’s not universal. It won’t work in these cases:

• Temporary or system tables
• Tables with media or MediaSet fields
• Tables with delete event subscribers
• Inside try functions
• When security filters or FlowField filters are applied
• When too many records are marked

Also, because it locks the entire table during the transaction, it allows less concurrency than DeleteAll. So plan your execution windows carefully.

Best Practices

Here’s how to get the most out of Rec.Truncate:

• Use it only when most of the table is being deleted. For partial deletes, DeleteAll is usually more efficient.
• Always check the return value and fall back to DeleteAll if needed.
• Avoid filters involving FlowFields or marked records.
• Decide whether to reset AutoIncrement values based on your downstream logic.

Truncate vs DeleteAll: Quick Comparison

AL Code Examples

Here are a few patterns you’ll probably use:

Basic truncate with fallback

procedure ClearLogFast()
var
    Log: Record "My Log Table";
    ok: Boolean;
begin
    ok := Log.Truncate(false); // preserve AutoIncrement
    if not ok then
        Log.DeleteAll(false);
end;

Filtered truncate for “older than N days”

procedure PurgeOldStaging()
var
    Staging: Record "My Staging Table";
    cutoffDate: Date;
    ok: Boolean;
begin
    cutoffDate := CalcDate('<-90D>', WorkDate);
    Staging.SetRange("Posting Date", 0D, cutoffDate);
    ok := Staging.Truncate(true); // reset AutoIncrement
    if not ok then
        Staging.DeleteAll(false);
end;

Safety-first truncate

procedure SafeClear(var RecVar: Record "My Large Table")
var
    ok: Boolean;
begin
    ok := RecVar.Truncate(true);
    if not ok then
        RecVar.DeleteAll(false);
end;

Final Thoughts

Rec.Truncate is a welcome addition to the AL toolbox. It’s not a silver bullet, but when used correctly, it can save you a ton of time and make your data cleanup routines much more efficient. Just be sure to handle the edge cases and fallback gracefully when needed.

If you’re working with big tables and want to speed things up, this method is worth exploring.