What is the difference between SQL commands delete and truncate?

Both delete and truncate commands can be used to delete data (records) in SQL, so what is the difference between them? The following article will introduce to you the difference between delete and truncate commands. I hope it will be helpful to you.

What is the delete command?

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

Example: The following is a student table, containing 3 fields: student_id, name, marks

You can use the SQL 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 The command will delete the record with student_id 2. After executing this statement, the table will contain only two records: records with student_id 1 and 3.

What is the truncate command?

The truncate command helps in deleting all records from the table. It also removes the table structure from the database. So when the truncate command is executed on a specific table, it also initializes the primary key.

The following SQL command will delete all records in the student table. Additionally, it removes the table structure from the database.

truncate table student;

What is the difference between delete and truncate commands?

1. Command type

delete is a data manipulation language (DML) command; and 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 from the database and Table Structure.

3. Where clause

delete command supports WHERE clause, you can use the where clause with DELETE to filter and delete specific records; and, truncate command WHERE clause is not supported.

4. Locking

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; however, the truncate command cannot be used with indexed views.

6. Execution speed

Because the delete command maintains the log, the speed is very slow. However, since the truncate command maintains minimal logging records in the transaction log, it executes faster.

7. Table structure

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

8. Transaction space

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

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of What is the difference between SQL commands delete and truncate?. For more information, please follow other related articles on the PHP Chinese website!