Truncate() in AL: The Bulk‑Delete Superpower You’re Probably Ignoring
Why spend hours deleting millions of rows one by one? DeleteAll(false) still logs and locks every record. Truncate() bypasses all that noise, no logging, no triggers, just instant cleanup at the SQL level. Perfect for staging tables and nightly jobs that need speed without drama.
✖️ The Problem – When cleanup job crawl
A client once complained their nightly cleanup job “sometimes finished by breakfast.” The code wasn’t bad, it used DeleteAll(false) on a staging table with millions of processed rows.
But here’s the catch:
– Too slow — row‑by‑row deletes add up.
– Too much logging — every record removal is tracked.
– Too much lock noise — SQL spends time managing locks instead of clearing data.
– Too many SQL tears — the database groans under the weight of unnecessary work.
🛠️ The fix
Record.Truncate().
When you need to remove most rows from a normal table, Truncate() gives the platform a bulk-delete path instead of making SQL walk the table row by row.
➡️ Why it matters:
– Fewer SQL round-trips and less logging overhead during large purges.
– Shorter Job Queue windows and less collateral blocking.
– A clean fallback pattern when the platform says, “not on this table.”
‼️The trap:
Truncate() is not just a faster DeleteAll(). If the table has delete logic, delete subscribers, media fields, security filters, FlowField filters, or you are inside a try function, the platform can reject it. It also locks the whole table until commit, so concurrency can get worse, not better.
✅ Platform reality check
– SaaS: Yes. Microsoft documents it as a standard AL runtime method from runtime 16.0, and it is not marked as on-prem-only.
– Runtime vs Install/Upgrade: Runtime feature. Not limited to install or upgrade codeunits.
– Triggers: Treat it as bypassing the usual delete pipeline. If you rely on OnDelete or delete-event behavior, Truncate() is the wrong hammer. The global DatabaseDelete trigger is not called.
💡 The Bottom Line
If you are deleting most of a table, say so in code.
Do not make SQL roleplay as a row-by-row janitor.
** Are you still using DeleteAll() for bulk cleanup, or have you started adding Truncate() plus fallback patterns to your toolbox?