Microsoft Dynamics 365 Business Central 2025 Release Wave 2 (Version 27.0) introduces a new, more efficient way to delete large volumes of data: Truncate.

My first question is, how much faster is it?

I setup a little experiment. I created a routine to generate 500,000 records. Which took 127,676 millisecond or about 2 minutes. I then deleted the records in one of three different ways. Here is how they stack up.

Record.Delete() In a Loop

ARD_ItemStaging.SetFilter("ARD_No.", '<>0');
if ARD_ItemStaging.FindSet() then
    repeat
        ARD_ItemStaging.Delete();
    until ARD_ItemStaging.Next() = 0;

Deleting the records one at a time in a loop is the worst. Taking 648,894 milliseconds, that is nearly 11 minutes, it is by far the worst way to delete a lot of records.

Record.DeleteAll()

ARD_ItemStaging.setfilter("ARD_No.", '<>0');
if ARD_ItemStaging.FindSet() then
    ARD_ItemStaging.DeleteAll();

Switching to the much more efficient DeleteAll command takes our bulk delete down to 19,585 milliseconds, which is about 20 seconds. A vast improvement over the one record at a time methodology. Knowing a little about the SQL backend, this is a logical improvement.

Record.Truncate()

ARD_ItemStaging.setfilter("ARD_No.", '<>0');
if ARD_ItemStaging.FindSet() then
    ARD_ItemStaging.Truncate(true);

Now for Truncate, the new kid on the block, comes in at a blistering 166 milliseconds. 118 times faster than Delete All! I ran it twice to be sure.

Now before you go and do a find and replace on your DeleteAll statements, let’s cover the rules.

You can NOT use Truncate on:

• Tables with OnDelete triggers or event subscriptions.
• Tables that are not Normal, such as System or Temporary
• Tables with media or mediaset fields.
• Use inside a Try function.
• Use with filters on FlowFields.
• Use with Security Filters on the Table for the current user.

Because of these rules, it is recommended that you use a fail-through delete pattern like this:

ARD_ItemStaging.setfilter("ARD_No.", '<>0');
if ARD_ItemStaging.FindSet() then
    if NOT ARD_ItemStaging.Truncate(true) then
        ARD_ItemStaging.DeleteAll();

Also note that truncate locks the entire table until it is done.

Now reviewing all the rules, and the table locking, we can get an idea why it is so fast. By skipping Triggers and Event Subscriptions as well as other row by row operations that get in the way, Truncate unencumbered by the checks and balances and processing requirements of a Delete or DeleteAll procedure.

It is great to have options and when clearing out a lot of data and Truncate may be a way to save a lot of time. Let me know what you think in the comments.

More details in the Microsoft Learn: Truncate table data with new AL method | Microsoft Learn