Stats

103 Contributors: 9 Wednesday, June 21, 2017
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap
Download eBook

TRUNCATE

Download sql eBook

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

Related Examples