SQL Server中DELETE和TRUNCATE的區別

  • 2021 年 2 月 23 日
  • 筆記

​DELETE和TRUNCATE語句之間的區別是求職面試中最常見的問題之一。這兩條語句都可以從表中刪除數據。然而，也有不同之處。

本文將重點討論這些差異，並通過實例加以說明。

TRUNCATE

DELETE

從表中刪除所有記錄。我們不能使用WHERE刪除特定的記錄

刪除所有記錄，並可以使用WHERE刪除特定記錄。

不觸發DELETE觸發器。

觸發DELETE觸發器

重置標識列

不重置標識列

由於日誌很少，所以速度更快。

由於執行了表掃描，以計算要刪除的行數，並逐個刪除行，所以會更慢。更改被記錄在事務日誌中。

使用行級鎖

使用表級鎖

需要ALTER TABLE許可權

需要表的DELETE 許可權

出於演示的目的，我創建了一個名為studentDB的表。除此之外，又創建了兩個表，tblSchool和tblStudent，並在這兩個表中插入了一些記錄。

下面的語句創建了tblStudent表:

CREATE TABLE [dbo].[tblStudent](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [student_name] [varchar](250) NOT NULL,
  [student_code] [varchar](5) NOT NULL,
  [student_grade] [char](2) NOT NULL,
  [SchoolID] [int] NOT NULL,
 CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED 
( [ID] ASC))
GO
ALTER TABLE [dbo].[tblStudent]  WITH CHECK ADD  CONSTRAINT [FK_tblStudent_tblSchool] FOREIGN KEY([SchoolID])
REFERENCES [dbo].[tblSchool] ([School_ID])
GO
​
ALTER TABLE [dbo].[tblStudent] CHECK CONSTRAINT [FK_tblStudent_tblSchool]
GO

下面的語句創建了tblSchool表:

CREATE TABLE [dbo].[tblSchool](
  [School_ID] [int] IDENTITY(1,1) NOT NULL,
  [School_Name] [varchar](500) NULL,
  [City] [varchar](50) NULL,
CONSTRAINT [PK_tblSchool] PRIMARY KEY CLUSTERED 
([School_ID] ASC)) ON [PRIMARY]
GO

將數據插入到tblStudent表中:

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 
values 
('Nisarg Upadhyay','ST001','A',1),
('Nirali Upadhyay','ST002','B',1),
('Dixit Upadhyay','ST003','A',1),
('Bharti Upadhyay','ST004','C',2),
('Nimesh Patel','ST005','C',2),
('Raghav Dave','ST006','A',1)
Go

將數據插入到tblSchool表中:

insert into [dbo].[tblSchool] ([school_name], [city]) 
values 
('Nalanda School','Mehsana'),
('Sarvajanik School','Mehsana')

現在，我們來看看兩者的不同之處。

1. 刪除數據

DELETE命令的作用是從表中刪除特定的/所有記錄。TRUNCATE語句刪除所有數據。

  • DELETE

要使用DELETE刪除特定記錄，可以在查詢中使用WHERE子句。假設我們想從tblstudent表中刪除一些學生，該學生的程式碼是ST002。在DELETE語句中添加如下條件:

Delete from  tblstudent where student_code='ST002'

執行後，將只刪除表中的一條記錄。一旦記錄被刪除，運行select查詢查看數據:

  • TRUNCATE

在truncate中，添加WHERE子句是不可能的。下面的查詢刪除了tblStudent表中的所有記錄:

Truncate table tblStudent

2. 觸發器

當我們運行DELETE語句時，SQL會調用DELETE觸發器。

我已經在tblStudent上創建了一個名為trgdeleteStudent的觸發器。當對tblstudent表執行DELETE語句時，觸發器將在tblDeletedStudent表中插入一條記錄。

創建tbldeletedStudent的T-SQL程式碼如下:

CREATE TABLE [dbo].[tblDelatedStudents]
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Student_Code] [varchar](10) NULL,
  CONSTRAINT [PK_tblDelatedStudents] PRIMARY KEY CLUSTERED ([ID] ASC)
)

下面的程式碼創建了觸發器:

create TRIGGER trgdeleteStudent on [tblStudent]
FOR DELETE 
AS 
INSERT INTO [dbo].[tblDelatedStudents](student_code)
  SELECT student_code
FROM DELETED;
GO

運行以下查詢刪除學生ST0001的記錄:

delete from tblstudent where student_code='ST001'

執行以下查詢命令進行驗證:

select * from [dbo].[tblDelatedStudents] 

正如上面的截圖中看到的，一條記錄被添加到tblDelatedStudents表中。

現在，讓我們運行TRUNCATE TABLE語句來刪除tblstudent表中的數據:

Truncate table [dbo].[tblDelatedStudents]

通過查詢tblDeletedStudent來驗證數據:

select * from [dbo].[tblDelatedStudents]

​

如你所見，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]) 
values 
('Ramesh Upadhyay','ST007','B',2)
Go

執行如下查詢命令查看tblStudent的數據:

select * from [dbo].[tblStudent]

這裡顯示初始標識列值加1。

  • TRUNCATE

執行以下TRUNCATE語句刪除tblStudent表中的數據:

Truncate table [dbo].[tblStudents]

刪除數據後，在表中插入記錄:

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 
values 
('Nisarg Upadhyay','ST001','A',1),
('Nirali Upadhyay','ST002','B',1),
('Dixit Upadhyay','ST003','A',1),
('Bharti Upadhyay','ST004','C',2),
('Nimesh Patel','ST005','C',2),
('Raghav Dave','ST006','A',1)
Go

執行如下查詢命令查看tblStudent的數據:

select * from [dbo].[tblStudent]

綜上所述，標識列被重置了。

4. 許可權

要使用DELETE語句刪除數據，必須對錶具有DELETE許可權。要使用TRUNCATE TABLE語句刪除數據，我們需要ALTER TABLE許可權。

  • DELETE

我已經創建了一個名為testuser1的用戶，並在tblStudent表上分配了刪除許可權。

我們刪除student_code=ST001的學生記錄:

use StudentDB
go
delete from tblstudent where student_code='ST001'

接著查看tblStudent的數據:

它確實從表中刪除了記錄。

  • TRUNCATE

現在，運行TRUNCATE刪除數據:

use StudentDB
go
truncate table tblstudent

查詢返回如下錯誤:

Msg 1088, Level 16, State 7, Line 3
Cannot find the object "tblstudent" because it does not exist or you do not have permissions

為了糾正這個問題，我們必須分配ALTER TABLE許可權。執行以下查詢，授予tblStudent表訪問許可權:

grant ALTER on tblstudent to testuser1

重新執行truncate：

use StudentDB
go
truncate table tblstudent

結果如下：

表中的數據被刪除。

本文解釋了DELETE語句和TRUNCATE 之間的區別。我們指出了常見的區別，並通過示例進行了說明。

原文鏈接：//codingsight.com/difference-between-delete-and-truncate-table-in-sql-server/

​

VirMach 便宜 VPS

QNews

QNews