TRUNCATE vs. DELETE in SQL: What are the Key Differences?

In-depth understanding of the differences between TRUNCATE and DELETE in SQL

The TRUNCATE and DELETE commands in SQL are both used to delete data in a table, but their methods are different. TRUNCATE usually provides a faster and more thorough way of deleting data.

Statement Type

Unlike DELETE which is DML (Data Manipulation Language), TRUNCATE belongs to DDL (Data Definition Language).

Commit and rollback

Command rollback capabilities vary by database:

• SQL Server and PostgreSQL: Both allow rollback of TRUNCATE.
• Oracle: TRUNCATE as a DDL operation involves two commits, making it irreversible. However, Oracle's Flashback feature can partially alleviate this problem.

Space Recycling

DELETE leaves free space, while TRUNCATE reclaims it. Oracle's REUSE STORAGE option allows data segments to be preserved, improving efficiency when reloading tables.

Row range

DELETE allows selective deletion of rows, while TRUNCATE deletes all rows unconditionally. Oracle allows truncation of specific partitions in partitioned tables.

Object Type

DELETE works on tables and clustered tables, but TRUNCATE is limited to tables or the entire cluster (Oracle specific).

Data object identifier

In Oracle, DELETE retains the data object ID, while TRUNCATE assigns a new ID unless no insert operations have occurred since the table was created.

Flashback (Oracle)

DELETE supports the Flashback feature, but TRUNCATE blocks it unless the FLASHBACK ARCHIVE feature is used.

Permissions

DELETE permission can be granted to the user, but TRUNCATE requires DROP ANY TABLE authorization (Oracle specific).

Performance Notes

• Redo/Undo: DELETE generates a lot of undo data, while TRUNCATE takes up negligible space.
• Index: TRUNCATE restores a disabled index (Oracle specific).
• Foreign Keys: If a foreign key refers to the table, TRUNCATE is prohibited and the behavior of DELETE depends on the configuration.
• Table locking: TRUNCATE requires exclusive locks (Oracle specific), while DELETE prefers shared locks.

Triggers and Remote Execution

TRUNCATE supports DDL triggers (Oracle specific). Oracle does not allow remote TRUNCATE execution.

Identity column

In SQL Server, TRUNCATE resets the identity column sequence, but DELETE does not.

Result set

In Oracle PL/SQL, the DELETE statement can return deleted rows to the client.

The above is the detailed content of TRUNCATE vs. DELETE in SQL: What are the Key Differences?. For more information, please follow other related articles on the PHP Chinese website!