Business Central 2025 wave 2 (BC27): Truncating table data with new AL method (procedure Truncate)

Dynamics 365 Business Central

Hi, Readers.
The public preview for Dynamics 365 Business Central 2025 release wave 2 (BC27) is available. Learn more: Link.

I will continue to test and share some new features that I hope will be helpful. In this post, I would like to talk about Truncate table data with new AL method.

Truncate table data with new AL method:

Business value:

The new Rec.Truncate method in AL provides a powerful and efficient way to delete large volumes of data from a table. By bypassing row-by-row deletions, this method significantly improves performance and reduces execution time for data cleanup or reset operations. The method also offers flexibility with options to retain or reset AutoIncrement values and ensures safe usage by enforcing delete permissions. This feature is particularly valuable for scenarios involving bulk data management, helping you maintain optimal system performance and data hygiene.

https://learn.microsoft.com/en-us/dynamics365/release-plan/2025wave2/smb/dynamics365-business-central/truncate-table-data-new-al-method?wt.mc_id=DX-MVP-5004336

The new AL method Rec.Truncate is available with the record API. This method lets developers delete table rows, matching filters in the most efficient way. This has always been available in databases, such as SQL, more details: TRUNCATE TABLE (Transact-SQL)

procedure Truncate([ResetAutoIncrement: Boolean]): Boolean

Deletes all records in a table that fall within a specified range, in an efficient maner. Keep in mind that Truncate allows for less concurrency than DeleteAll, as the entire table will be locked until the transaction is committed.

Truncate with a filter should only be used when the majority of the table is being deleted, as otherwise DeleteAll will be more efficient.

Truncate is currently not supported in the following cases:

  • Temporary tables, system tables, and tables of type other than Normal.
  • When running within a try function.
  • Tables that have a security filter applied.
  • When the current filters contain flow fields, or use a high number of marked records.
  • When the OnAfter/OnBefore events are subscribed for the table.
  • Tables with media fields.

In this case, it is recommended to use DeleteAll instead.

The method has parameters that allow you to:

  • Check whether the table doesn’t support truncation.
  • Set AutoIncrement fields to the value they had before truncating or reset to 0.

Here is a very simple example:

Test video:

And Truncate is currently not supported in the following cases:

  • Temporary tables, system tables, and tables of type other than Normal.

Truncate is only supported for normal tables.

  • When running within a try function.

Truncate is not supported in try functions.

  • Tables that have a security filter applied.

A security filter has been set for table ZY Book. This is not allowed for the current function.

  • When the current filters contain flow fields, or use a high number of marked records.

If the method is used with filters, the platform copies data to a temporary table and moves it back after truncation. Only use filters when you’re deleting significant numbers of rows—using filters for smaller deletions creates unnecessary overhead. In the current version, when deleting a large amount of data, as long as you add a filter or FindSet(), a runtime error will occur.

Another example:

  • When the OnAfter/OnBefore events are subscribed for the table.

Truncate is not supported when the OnBeforeDelete and/or OnAfterDelete event is subscribed. Please remove the events subscriptions

  • Tables with media fields.

Truncate is not supported when the field has Media and/or MediaSet fields.

Great, give it a try!!!😁

END

Hope this will help.

Thanks for reading.

ZHU

コメント