TRUNCATE TABLE statement in SQL

TRUNCATE TABLE is the SQL statement to remove all records/rows from a table without affecting its schema and structure. Unlike the DELETE FROM statement, TRUNCATE TABLE does not support a WHERE clause, so specific records cannot be deleted. However, TRUNCATE TABLE does not need to scan all the rows before deleting them, so the execution will be faster and more efficient in the case that we want to delete all the records of a table while maintaining its structure.

This is where its main difference with the DROP TABLE statement lies, since it eliminates both the records and the structure and definition of the table itself, while TRUNCATE TABLE only eliminates the records, but keeps intact the structure and definition of the table.

The SQL statement is very simple: TRUNCATE TABLE tableName, being also able to add the database and/or schema to which the table belongs as a prefix.

TRUNCATE TABLE store.dbo.Orders;

TRUNCATE TABLE will not accept the IF EXISTS argument in its statement definition. However, when we have partitioned tables, it will accept the WITH PARTITIONS argument where we will specify which particular partition to truncate. Based on this argument, we must take into account the following considerations:

• When our table is not partitioned and we try to use the WITH PARTITIONS argument, the TRUNCATE TABLE statement will give us an error like the example below.

TRUNCATE TABLE store.dbo.Orders WITH (PARTITIONS (1));

• In the event that our table was partitioned, we could use the WITH PARTITIONS argument in the following ways:

  1.If we want to delete a single specific partition, we can specify it in the definition:

TRUNCATE TABLE tableName WITH (PARTITIONS (2));

2. If we want to eliminate more than one partition, we can specify them from a single statement:

TRUNCATE TABLE tableName WITH (PARTITIONS (1,5));

3. We can also define a range to eliminate several partitions:

TRUNCATE TABLE tableName WITH (PARTITIONS (2 TO 5));

4. It is also possible to specify multiple partitions and a range from the same statement:

TRUNCATE TABLE tableName WITH (PARTITIONS (2,3,5 TO 7));

TRUNCATE TABLE has some limitations that should be noted and considered. Among them, the most important is that this statement cannot be used on tables referenced by a Foreign Key restriction to another table. To exemplify this case, suppose we wanted to truncate the Customers table whose customerId field is defined as a Foreign Key in the Orders table.

TRUNCATE TABLE Customers;

In conclusion, TRUNCATE TABLE is an excellent option when we want to reset all the data in a table without deleting it from the system, and we must take it into account and prioritize it over the DELETE FROM alternative, since we will save resources and speed up the execution.