---
id: post/duiliotacconi-com/1850
type: post
title: Rec.Truncate in AL
summary: 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.
tier: community
language: en
tags:
  - rec.truncate
  - al statement
  - table deletion
  - performance
  - sql server
  - database optimization
  - constraints
system: development
review:
  state: reviewed
  by: opus
  at: "2026-10-08T01:57:04.179Z"
  flags: []
generated:
  at: "2026-10-08T01:26:53.141Z"
  pipeline: 0.2.0
  prompts:
    extract-post: 1
  input_hash: 7f7ad36088b3de44ed506c671ac58de3466b569754bbbab7a0b80ad2b74aedae
evidence:
  - kind: blog
    url: https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/
    title: Rec.Truncate in AL
    date: "2026-02-10"
    commit: null
    t: null
    quote: null
  - kind: blog
    url: https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/
    title: Rec.Truncate in AL
    date: "2026-02-10"
    commit: null
    t: null
    quote: when cleaning a table with few indexes, the truncate method becomes advantageous if deleting at least 50% - 60% of the content
  - kind: blog
    url: https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/
    title: Rec.Truncate in AL
    date: "2026-02-10"
    commit: null
    t: null
    quote: If filtered, check the number of records to be deleted and decide what to do.
links:
  learn: []
  objects: []
  features: []
  topics: []
  localizations: []
  videos: []
  posts: []
  guidelines: []
post_id: "1850"
source_id: duiliotacconi-com
source_name: Duilio Tacconi
url: https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/
published_at: "2026-02-10T16:34:20.000Z"
author: Duilio Tacconi
full_text: false
words: 1186
quotes:
  - text: when cleaning a table with few indexes, the truncate method becomes advantageous if deleting at least 50% - 60% of the content
    why_it_matters: Provides the critical threshold at which truncate outperforms DeleteAll, essential for deciding which method to use
  - text: If filtered, check the number of records to be deleted and decide what to do.
    why_it_matters: Highlights that truncate with filters requires careful analysis because it can become slower than DeleteAll when deleting small portions
code_objects_mentioned: []
systems:
  - development
  - platform
  - administration
versions_mentioned:
  - BC27
preview:
  embeddable: true
  frame_url: https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/embed/
  image: https://duiliotacconi.com/wp-content/uploads/2026/02/mariemoreau.png
  image_alt: null
  image_w: 736
  image_h: 331
  site_name: Dynamics 365 Business Central tales, myths, legends. And something serious.
  favicon: https://duiliotacconi.com/wp-content/uploads/2023/07/cropped-wordpress.png?w=32
  probed_at: "2026-10-07T11:50:38.759Z"
---

# Rec.Truncate in AL

[Read the post](https://duiliotacconi.com/2026/02/10/rec-truncate-in-al/) · Duilio Tacconi (Duilio Tacconi, MVP) · 2026-02-10 · 1186 words · tier community · reviewed (checked by Opus)

> 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.

## 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.