The difference between delete and truncate in SQL Server

​DELETE and TRUNCATE The difference between sentences is one of the most common questions in job interviews . Both statements can remove data from a table . However , There are also differences .

This article will focus on these differences , It is illustrated by examples .

TRUNCATE	DELETE
Delete all records from the table . We can't use WHERE Delete specific records	Delete all records , And can be used WHERE Delete specific records .
Don't trigger DELETE trigger .	Trigger DELETE trigger
Reset identity column	Do not reset the identity column
Because there are very few logs , So it's faster .	Because a table scan was performed , To calculate the number of rows to delete , And delete the rows one by one , So it's going to be slower . Changes are recorded in the transaction log .
Using row level locks	Using table level locks
need ALTER TABLE jurisdiction	You need a watch DELETE  jurisdiction

For demonstration purposes , I created a project called studentDB Table of . besides , Two more tables were created ,tblSchool and tblStudent, And insert some records into these two tables .

The following statement creates tblStudent surface :

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

The following statement creates tblSchool surface :

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

Insert data into tblStudent In the table :

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

Insert data into tblSchool In the table :

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

Now? , Let's look at the difference between the two .

1.  Delete data

DELETE The function of the command is to remove a specific / All records .TRUNCATE Statement to delete all data .

• DELETE

To use DELETE Delete specific records , You can use... In queries WHERE Clause . Let's say we want to go from tblstudent Delete some students from the table , The student's code is ST002. stay DELETE Add the following condition to the statement :

Delete from  tblstudent where student_code='ST002'

After execution , Only one record in the table will be deleted . Once the record is deleted , function select Query and view data :

• TRUNCATE

stay truncate in , add to WHERE Clause is impossible . The following query was deleted tblStudent All records in the table :

Truncate table tblStudent

2.  trigger

When we run DELETE When the sentence is ,SQL Would call DELETE trigger .

I am already in tblStudent A new one named trgdeleteStudent The trigger of . When the tblstudent Table execution DELETE When the sentence is , The trigger will be in tblDeletedStudent Insert a record in the table .

establish tbldeletedStudent Of T-SQL The code is as follows :

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

The following code creates the trigger :

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

Run the following query to delete students ST0001 The record of :

delete from tblstudent where student_code='ST001'

Execute the following query command to verify :

select * from [dbo].[tblDelatedStudents]

 

As you can see in the screenshot above , A record is added to tblDelatedStudents In the table .

Now? , Let's run TRUNCATE TABLE Statement to delete tblstudent Table data :

Truncate table [dbo].[tblDelatedStudents]

By inquiring tblDeletedStudent To verify the data :

select * from [dbo].[tblDelatedStudents]

 

As you can see ,tblDeletedStudent There are no records inserted in the table . therefore ,trgdeletestudent The trigger is not triggered .

3.  Reset identity column

When executed DELETE When the sentence is , The identity column is not reset to the initial value . about TRUNCATE sentence , The identity column will be reset .

• DELETE

perform DELETE Statement delete tblStudent Table data :

delete from tblStudent where student_code='ST004'

then , Perform the following insert sentence , To watch tblStudent Add records :

insert into [dbo].[tblStudent] ([student_name],[student_code],[student_grade],[SchoolID]) 
values 
('Ramesh Upadhyay','ST007','B',2)
Go

Execute the following query command to view tblStudent The data of :

select * from [dbo].[tblStudent]

 

The initial identity column value plus 1.

• TRUNCATE

Perform the following TRUNCATE Statement delete tblStudent Table data :

Truncate table [dbo].[tblStudents]

After deleting data , Insert records into the table :

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

Execute the following query command to view tblStudent The data of :

select * from [dbo].[tblStudent]

 

in summary , The identity column has been reset .

4.  jurisdiction

To use DELETE Statement delete data , Must have... On the table DELETE jurisdiction . To use TRUNCATE TABLE Statement delete data , We need to ALTER TABLE jurisdiction .

• DELETE

I've created one called testuser1 Users of , And in tblStudent The delete permission is assigned to the table .

We delete student_code=ST001 Student records of :

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

Then check tblStudent The data of :

It does delete records from the table .

• TRUNCATE

Now? , function TRUNCATE Delete data :

use StudentDB
go
truncate table tblstudent

The query returned the following error :

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

To correct the problem , We have to allocate ALTER TABLE jurisdiction . Perform the following query , grant tblStudent Table access :

grant ALTER on tblstudent to testuser1

Re execution truncate：

use StudentDB
go
truncate table tblstudent

give the result as follows ：

 

The data in the table is deleted .

This article explains DELETE Statement and TRUNCATE The difference between . We pointed out the common differences , It is illustrated by an example .

Link to the original text ：https://codingsight.com/difference-between-delete-and-truncate-table-in-sql-server/