SQL Server中DELETE和TRUNCATE的区别

Easter79
• 阅读 552

DELETE和 TRUNCATE语句之间的区别是求职面试中最常见的问题之一。 这两条语句都可以从表中删除数据。 然而,也有不同之处。

本文将重点讨论这些差异,并通过实例加以说明。

TRUNCATE

DELETE

从表中删除所有记录。我们不能使用WHERE删除特定的记录

删除所有记录,并可以使用WHERE删除特定记录。

不触发DELETE触发器。

触发DELETE触发器

重置标识列

不重置标识列

由于日志很少,所以速度更快。

由于执行了表扫描,以计算要删除的行数,并逐个删除行,所以会更慢。更改被记录在事务日志中。

使用行级锁

使用表级锁

需要ALTER TABLE权限

需要表的DELETE 权限

出于演示的目的,我创建了一个名为studentDB的表。除此之外,又创建了两个表,tblSchool和tblStudent,并在这两个表中插入了一些记录。

下面的语句创建了tblStudent表:

CREATE TABLE [dbo].[tblStudent](

下面的语句创建了tblSchool表:

CREATE TABLE [dbo].[tblSchool](

将数据插入到tblStudent表中:

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 

将数据插入到tblSchool表中:

insert into [dbo].[tblSchool] ([school_name], [city]) 

现在,我们来看看两者的不同之处。

1. 删除数据

DELETE命令的作用是从表中删除特定的/所有记录。TRUNCATE语句删除所有数据。

  • DELETE

要使用DELETE删除特定记录,可以在查询中使用WHERE子句。假设我们想从tblstudent表中删除一些学生,该学生的代码是ST002。在DELETE语句中添加如下条件:

Delete from  tblstudent where student_code='ST002'

执行后,将只删除表中的一条记录。一旦记录被删除,运行select查询查看数据:

SQL Server中DELETE和TRUNCATE的区别

  • TRUNCATE

在truncate中,添加WHERE子句是不可能的。下面的查询删除了tblStudent表中的所有记录:

Truncate table tblStudent

2. 触发器

当我们运行DELETE语句时,SQL会调用DELETE触发器。

我已经在tblStudent上创建了一个名为trgdeleteStudent的触发器。当对tblstudent表执行DELETE语句时,触发器将在tblDeletedStudent表中插入一条记录。

创建tbldeletedStudent的T-SQL代码如下:

CREATE TABLE [dbo].[tblDelatedStudents]

下面的代码创建了触发器:

create TRIGGER trgdeleteStudent on [tblStudent]

运行以下查询删除学生ST0001的记录:

delete from tblstudent where student_code='ST001'

执行以下查询命令进行验证:

select * from [dbo].[tblDelatedStudents]

SQL Server中DELETE和TRUNCATE的区别

正如上面的截图中看到的,一条记录被添加到tblDelatedStudents表中。

现在,让我们运行TRUNCATE TABLE语句来删除tblstudent表中的数据:

Truncate table [dbo].[tblDelatedStudents]

通过查询tblDeletedStudent来验证数据:

select * from [dbo].[tblDelatedStudents]

SQL Server中DELETE和TRUNCATE的区别

如你所见,tblDeletedStudent表中没有插入记录。因此,trgdeletestudent触发器没有被触发。

3. 重置标识列

当执行DELETE语句时,标识列不会重置为初始值。对于TRUNCATE 语句,标识列将重置。

  • DELETE

执行DELETE语句删除tblStudent表中的数据:

delete from tblStudent where student_code='ST004'

然后,执行以下insert语句,向表tblStudent添加记录:

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 

执行如下查询命令查看tblStudent的数据:

select * from [dbo].[tblStudent]

SQL Server中DELETE和TRUNCATE的区别

这里显示初始标识列值加1。

  • TRUNCATE

执行以下TRUNCATE语句删除tblStudent表中的数据:

Truncate table [dbo].[tblStudents]

删除数据后,在表中插入记录:

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 

执行如下查询命令查看tblStudent的数据:

select * from [dbo].[tblStudent]

SQL Server中DELETE和TRUNCATE的区别

综上所述,标识列被重置了。

4. 权限

要使用DELETE语句删除数据,必须对表具有DELETE权限。要使用TRUNCATE TABLE语句删除数据,我们需要ALTER TABLE权限。

  • DELETE

我已经创建了一个名为testuser1的用户,并在tblStudent表上分配了删除权限。

我们删除student_code=ST001的学生记录:

use StudentDB

接着查看tblStudent的数据:

SQL Server中DELETE和TRUNCATE的区别

它确实从表中删除了记录。

  • TRUNCATE

现在,运行TRUNCATE删除数据:

use StudentDB

查询返回如下错误:

Msg 1088, Level 16, State 7, Line 3

为了纠正这个问题,我们必须分配ALTER TABLE权限。执行以下查询,授予tblStudent表访问权限:

grant ALTER on tblstudent to testuser1

重新执行truncate:

use StudentDB

结果如下:

SQL Server中DELETE和TRUNCATE的区别

表中的数据被删除。

本文解释了DELETE语句和TRUNCATE 之间的区别。我们指出了常见的区别,并通过示例进行了说明。

本文分享自微信公众号 - 码农译站(gh_c0d62fbb4bda)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4天前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Wesley13 Wesley13
2年前
SQL关于删除的三个语句:DROP、TRUNCATE、 DELETE 的区别
SQL关于删除的三个语句:DROP、TRUNCATE、DELETE的区别DROP:DROPTABLEtest;删除表test,并释放空间,将test删除的一干二净。TRUNCATE:TRUNCATEtest;删除表test里的内容,并释放空间,但不删除表的定义,表的结构还在。DELETE:
Stella981 Stella981
2年前
Hive 删除行, 表 ,清空表
删除行A表数据如下id(String)       name(String)\1                       aaa2                      bbb3                      ccc\
Stella981 Stella981
2年前
SQL Server中DELETE和TRUNCATE的区别
DELETE和TRUNCATE语句之间的区别是求职面试中最常见的问题之一。这两条语句都可以从表中删除数据。然而,也有不同之处。本文将重点讨论这些差异,并通过实例加以说明。TRUNCATEDELETE从表中删除所有记录。我们不能使用WHERE删除特定的记录删除所有记录,并可以使用WHERE删除特定记录。不触发DEL
Stella981 Stella981
2年前
Python MySQL Delete
删除记录可以使用“DELETEFROM”语句,从现有表中删除记录:示例删除地址为“Mountain21”的记录:importmysql.connectormydbmysql.connector.connect(host"localhost",user"你
Wesley13 Wesley13
2年前
mysql试题
drop,delete与truncate的区别:优先级:droptruncatedeleteTRUNCATE按行删除并不把删除操作记录记入日志保存(不可恢复)DELETE全部删除表内数据并可将删除操作记录在日志中,可以加where字句,可应用于table和viewDROP删除整个表(结构和数据)再插入时自增长id又
Wesley13 Wesley13
2年前
MySQL 清空表(truncate)与删除表中数据(delete) 详解
删除表信息的方式有两种:truncatetabletable\_name;delete\fromtable\_name;注:truncate操作中的table可以省略,delete操作中的\可以省略truncate、delete清空表数据的区别:1truncate是整体删除(速度较快),delete是逐条删
Wesley13 Wesley13
2年前
MySQL相关问题题
1.truncate、delete、drop的区别(1)truncate、drop是不可以rollback的,但是delete是可以rollback的。DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATETABLE则一次性地从表中删除所有的数据并不把单独的删
小万哥 小万哥
7个月前
SQL DELETE 语句:删除表中记录的语法和示例,以及 SQL SELECT TOP、LIMIT、FETCH FIRST 或 ROWNUM 子句的使用
SQLDELETE语句SQLDELETE语句用于删除表中的现有记录。DELETE语法sqlDELETEFROM表名WHERE条件;注意:在删除表中的记录时要小心!请注意DELETE语句中的WHERE子句。WHERE子句指定应删除哪些记录。如果省略WHERE
Easter79
Easter79
Lv1
今生可爱与温柔,每一样都不能少。
文章
2.8k
粉丝
5
获赞
1.2k