Dynamics 365 Business Central: finally we’ll have TRUNCATE table in SaaS.

In the last 3 years I wrote many times about the need to have the possibility to execute a massive data deletion on SaaS and expecially the need to have the SQL TRUNCATE operation exposed to developers. My last post post this topic was this, I discussed with Microsoft and I also shared an Idea (link here).

You know that good wine must ferment properly and probably these things also require the right amount of time. But now, I’m happy to announce that…

Yes, we’ll have TRUNCATE in Business Central version 27!

The new AL method Rec.Truncate will be available in Dynamics 365 Business Central version 27 linked to the Record API. This method lets developers delete table rows, matching filters in the most efficient way.

The method has parameters that will 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, so it’s not recommended).

To be executed, the Truncate method needs delete permission and no security filters on the table for the current user.

This will be extremely useful on many scenarios, expecially when you have large log tables or large tables with historical data, that you don’t need to maintain anymore (and where you have forgot to activare Retention Policies).

But the new Rec.Truncate method will have also some limitations…

The method doesn’t support:

• Tables with OnDelete triggers or event subscriptions (in this case a 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.

Huge thanks to the community that supported my Idea… together we win!

Original Post https://demiliani.com/2025/08/04/dynamics-365-business-central-finally-well-have-truncate-table-in-saas/