SQL TRUNCATE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Introduction

The TRUNCATE statement deletes all data from a table. This is similar to DELETE with no filter, but, depending on the database software, has certain restrictions and optimizations.

Syntax

  • TRUNCATE TABLE table_name;

Remarks

TRUNCATE is a DDL (Data Definition Language) command, and as such there are significant differences between it and DELETE (a Data Manipulation Language, DML, command). While TRUNCATE can be a means of quickly removing large volumes of records from a database, these differences should be understood in order to decide if using a TRUNCATE command is suitable in your particular situation.

  • TRUNCATE is a data page operation. Therefore DML triggers (ON DELETE) associated with the table won't fire when you perform a TRUNCATE operation. While this will save a large amount of time for massive delete operations, however you may then need to manually delete the related data.
  • TRUNCATE will release the disk space used by the deleted rows, DELETE will release space
  • If the table to be truncated uses identity columns (MS SQL Server), then the seed is reset by the TRUNCATE command. This may result referential integrity problems
  • Depending the security roles in place and the variant of SQL in use, you may not have the necessary permissions to perform a TRUNCATE command


Got any SQL Question?