SQL中删除和截断之间的区别 [英] Difference between delete and truncate in SQL

问题描述

在sql中而不是mysql中截断和删除之间的实际区别是什么.

我尝试过的事情:

删除
1. DELETE是DML命令.
2.使用行锁执行DELETE语句，表中的每一行都被锁定以进行删除.
3.我们可以在where子句中指定过滤器
4.如果存在条件，它将删除指定的数据.
5.删除会激活触发器，因为该操作是单独记录的.
6.比截断慢，因为它保留日志.
7.可以回滚.

截断
1. TRUNCATE是DDL命令.
2. TRUNCATE TABLE始终锁定表和页面，但不锁定每一行.
3.不能使用Where条件.
4.删除所有数据.
5. TRUNCATE TABLE无法激活触发器，因为该操作不会记录单个行的删除.
6.在性能上更快，因为它不保留任何日志.
7.无法回滚.

What is actual different between truncate and delete in sql not mysql.

What I have tried:

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn''t keep any logs.
7. Rollback is not possible.

推荐答案

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. Delete activates a trigger because the operation are logged individually.
5. Slower than truncate because, it keeps logs.
6. Rollback is possible.
7. If a column has identity(1,1) after delete it create next number  

TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is  possible.
8. It is sequence of drop table and create table
9.If a column has identity(1,1) after TRUNCATE it create from first,means from 1

这篇关于SQL中删除和截断之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！