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.

Feature details

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.

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.

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.

Truncate needs delete permission and no security filters on the table for the current user.

The method doesn’t support:

• Tables with OnDelete triggers or event subscriptions (runtime error occurs; to handle this, use IF Rec.Truncate THEN to fall back to DeleteAll or expose the error).
• Tables with media or mediaset fields.
• Use inside try functions.
• Use with filters on FlowFields.

Geographic areas

Visit the Explore Feature Geography report for Microsoft Azure areas where this feature is planned or available.

Language availability

Visit the Explore Feature Language report for information on this feature's availability.

Tell us what you think

Help us improve Dynamics 365 Business Central by discussing ideas, providing suggestions, and giving feedback. Use the forum at https://aka.ms/bcideas.