Shulou(Shulou.com)05/31 Report--

This article is about the difference between delete and truncate in sql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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

Differences between 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.

Thank you for reading! This is the end of the article on "what's the difference between delete and truncate in sql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.