What's the Difference Between TRUNCATE and DELETE in SQL?

Detailed explanation of the difference between TRUNCATE and DELETE in SQL

When deleting data in a database table, there are two main options: TRUNCATE and DELETE. Although both commands can delete lines, they have different characteristics that require careful consideration.

Overview

If the goal is to quickly delete all rows from the table and there are no foreign key constraints blocking the operation, TRUNCATE is faster than DELETE. However, system-specific factors may alter this dynamic.

Statement types and execution

Contrary to the DML (Data Manipulation Language) nature of DELETE, TRUNCATE belongs to DDL (Data Definition Language). This fundamental difference affects their behavior and meaning.

Commit and rollback

The ability to rollback statements is platform dependent. While some implementations allow rollback of truncation operations, other implementations, especially those using a DDL approach, disallow rollback.

Space recycling

A key difference is their impact on storage space. DELETE only deletes rows, while TRUNCATE performs a comprehensive space reclamation process that may free up a large amount of disk space.

Operating range

TRUNCATE deletes all rows in a table at once, while DELETE provides the flexibility to select specific rows to delete.

Object Type

TRUNCATE typically applies to tables, while DELETE can operate on both tables and table clusters (if available).

Data object identifier

In some implementations, TRUNCATE assigns a new data object identifier to the table, while DELETE does not affect it.

Flashback and Undo

Flashback technology (designed to recover lost data) can be used with deletion, but not with truncation.

Permissions

Authorization mechanisms may vary between implementations. For example, granting DELETE permission on a table is allowed, but truncation of the table may require higher permissions.

Redo and undo log generation

TRUNCATE generates very few redo and undo log entries compared to the massive logs generated by DELETE.

Index Impact

In some cases, TRUNCATE can make an unavailable index available again, while DELETE does not have this functionality.

Foreign Keys and Table Locking

Truncation may not be allowed for tables with foreign key references enabled. The dynamics of table locking also vary, with TRUNCATE typically requiring an exclusive lock, while DELETE can use a shared lock.

Trigger

DML triggers (executed in response to data manipulation statements) will not fire during truncation.

Remote execution

Some implementations (such as Oracle) limit the ability to execute TRUNCATE statements over a database link.

Self-added column

In some implementations, truncating a table with auto-incrementing columns resets the sequence of those columns, while DELETE does not.

Result set

DELETE can return deleted rows to the client, while TRUNCATE does not have this functionality.

The above is the detailed content of What's the Difference Between TRUNCATE and DELETE in SQL?. For more information, please follow other related articles on the PHP Chinese website!