What is the difference between the SQL command delete and truncate

This article mainly introduces what is the difference between the SQL command delete and truncate, the article is very detailed, has a certain reference value, interested friends must read it!

Both the delete and truncate commands can be used to delete data (records) in SQL, so what's the difference between them?

What is the delete command?

The delete command helps delete records from tables in the database. [video tutorial recommendation: MySQL tutorial]

Example: the following is a student table with three fields: student_id, name, marks

You can use SQL's delete command to delete all records in the student table:

Delete from student

You can also delete specific records in the student table:

Delete from student where student_id = 2

The above SQL command deletes the record with student_id 2. After the statement is executed, the table will contain only two records: records with student_id 1 and 3.

What is the truncate command?

The truncate command helps delete all records from the table. It also removes the table structure from the database. Therefore, when the truncate command is executed on a particular table, it also initializes the primary key.

The following SQL command deletes all records in the student table. In addition, it removes the table structure from the database.

Truncate table student

What is the difference between the delete and truncate commands?

1. Command type

Delete is a data manipulation language (DML) command; truncate is a data definition language (DDL) command.

2. Function

The delete command deletes single, multiple, or all records from the table according to the specified SQL statement, while the truncate command deletes all records and table structures from the database.

3. Where clause

The delete command supports the WHERE clause, and you can use the where clause with DELETE to filter and delete specific records, while the truncate command does not support the WHERE clause.

4. Lock

The delete command uses row-level locking, and each row in the table is locked for deletion; the truncate command uses table-level locking, locking the entire table to delete all records.

5. Indexed view

The delete command can be used with indexed views, while the truncate command cannot be used with indexed views.

6. Execution speed

Because the delete command maintains logs, it is slow. However, because the truncate command maintains the fewest log records in the transaction log, it executes faster.

7. Table structure

The delete command does not affect the table structure, while the truncate command removes the table structure from the database.

8. Transaction space

The delete command uses more transaction space than the truncate command.

These are all the differences between the SQL command delete and truncate. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!