BC Observatoryunofficial, made by waldo
community postAL Developmentcommunity - not Microsoftreviewed - checked by Opus

Rec.Truncate in AL

Rec.Truncate is a new AL method based on SQL Server TRUNCATE TABLE that empties a table in milliseconds rather than deleting row by row. If a constraint is hit (system or virtual table, try function, security filters, delete event subscribers, Media fields, many marked rows, FlowField filters) it falls back to slow DeleteAll behavior. With filters it saves rows to keep and restores them, which only pays off when removing about 50-60% or more.

Open in Claude1850.md

post Dynamics 365 Business Central tales, myths, legends. And something serious.

Duilio Tacconi · 2026-02-10 · 1,186 words · about 5 min

"when cleaning a table with few indexes, the truncate method becomes advantageous if deleting at least 50% - 60% of the content"
Show the post hereRead on Dynamics 365 Business Central tales, myths, legends. And something serious.

Key points

  • Truncating a whole table without filters is far faster than DeleteAll, and the gap grows with more records, indexes and SIFTs
  • Listed constraints such as active security filters, OnBeforeDelete/OnAfterDelete subscribers, Media fields, try functions, many marked rows or FlowField filters make it fall back to row-by-row deletion
  • Filtered truncate copies rows to keep into a temp table, disables SIFTs, truncates, then restores rows and rebuilds indexes; deleting a small slice was dramatically slower than DeleteAll in the demo
  • Lab tests on a one million row Item Ledger Entry clone showed truncate pays off only when deleting at least 50-60% of a table with few indexes
  • Best suited to staging and log tables that are emptied completely; test all scenarios before relying on it

Quotes

  • “when cleaning a table with few indexes, the truncate method becomes advantageous if deleting at least 50% - 60% of the content” (Provides the critical threshold at which truncate outperforms DeleteAll, essential for deciding which method to use)
  • “If filtered, check the number of records to be deleted and decide what to do.” (Highlights that truncate with filters requires careful analysis because it can become slower than DeleteAll when deleting small portions)

Context

  • Features: Rec.Truncate statement, Fast table clearing, Filtered deletion, Security filters, OnBeforeDelete/OnAfterDelete events, Media field handling, FlowField filters, Database page management
  • Versions: BC27

Source: Duilio Tacconi, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.