The difference between DELETE and TRUNCATE statements is one of the most common questions asked in job interviews. Both statements can delete data from a table. However, there are differences.

This article will focus on these differences and illustrate them with examples.

TRUNCATE

DELETE

Deletes all records from the table. We cannot delete specific records using WHERE

Delete all records, and you can delete specific records using WHERE.

The DELETE trigger is not triggered.

Trigger DELETE trigger

Reset identity column

Do not reset the identity column

With fewer logs, it is faster.

This is slower because a table scan is performed to count the number of rows to be deleted and to delete rows one by one. Changes are recorded in the transaction log.

Use row-level locking

Use table-level locks

ALTER TABLE permissions are required

DELETE permission for the table is required

For demonstration purposes, I created a table called studentDB. In addition, two more tables are created, tblSchool and tblStudent, and some records are inserted into these two tables.

The following statement creates the tblStudent table:

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] GOCopy the code

The following statement creates the tblSchool table:

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] GOCopy the code

Insert data into tblStudent 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
Copy the code

Insert data into tblSchool table:

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

Now, let’s look at the differences.

1. Delete data

The DELETE command deletes specific/all records from a table. The TRUNCATE statement deletes all data.

  • DELETE

To DELETE a particular record using DELETE, use the WHERE clause in the query. Suppose we want to delete some students from the TBLStudent table with the code ST002. Add the following conditions to the DELETE statement:

Delete from  tblstudent where student_code='ST002'
Copy the code

After execution, only one record in the table is deleted. Once the record has been deleted, run a SELECT query to see the data:

  • TRUNCATE

In TRUNCate, it is not possible to add a WHERE clause. The following query deletes all records from the tblStudent table:

Truncate table tblStudent
Copy the code

2. The trigger

When we run the DELETE statement, SQL invokes the DELETE trigger.

I have created a trigger called trgdeleteStudent on tblStudent. When a DELETE statement is executed on the TBLStudent table, the trigger inserts a record into the tblDeletedStudent table.

The t-SQL code for creating a tbldeletedStudent 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) )Copy the code

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
Copy the code

Delete student ST0001’s record by running the following query:

delete from tblstudent where student_code='ST001'
Copy the code

Run the following command to verify:

select * from [dbo].[tblDelatedStudents]
Copy the code

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

Now, let’s run the TRUNCATE TABLE statement to delete data from the TBLStudent TABLE:

Truncate table [dbo].[tblDelatedStudents]
Copy the code

Verify data by querying tblDeletedStudent:

select * from [dbo].[tblDelatedStudents]
Copy the code

As you can see, there are no records inserted in the tblDeletedStudent table. Therefore, the TRgdeleteStudent trigger is not fired.

3. Reset the identity column

When a DELETE statement is executed, the identity column is not reset to its initial value. For TRUNCATE statements, the identity column is reset.

  • DELETE

Alter table tblStudent DELETE from tblStudent;

delete from tblStudent where student_code='ST004'
Copy the code

Then, execute the following INSERT statement to add records to table tblStudent:

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

Run the following query command to view tblStudent data:

select * from [dbo].[tblStudent]
Copy the code

This shows the initial identity column value plus 1.

  • TRUNCATE

Execute the following TRUNCATE statement to delete data from the tblStudent table:

Truncate table [dbo].[tblStudents]
Copy the code

Insert a record into a table after deleting data:

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
Copy the code

Run the following query command to view tblStudent data:

select * from [dbo].[tblStudent]
Copy the code

In summary, the identity column has been reset.

4. The permissions

To DELETE data using DELETE statements, you must have DELETE permission on the table. To delete data using the TRUNCATE TABLE statement, we need the ALTER TABLE privilege.

  • DELETE

I have created a user named testUser1 and assigned delete permissions on the tblStudent table.

Student_code =ST001;

use StudentDB
go
delete from tblstudent where student_code='ST001'
Copy the code

Then look at the tblStudent data:

It does delete the record from the table.

  • TRUNCATE

Now, run TRUNCATE to delete data:

use StudentDB
go
truncate table tblstudent
Copy the code

The following error is returned:

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

To correct this problem, we must assign ALTER TABLE permissions. Grant tblStudent table access by executing the following query:

grant ALTER on tblstudent to testuser1
Copy the code

Re-execute TRUNCate:

use StudentDB
go
truncate table tblstudent
Copy the code

The results are as follows:

The data in the table is deleted.

This article explains the difference between a DELETE statement and TRUNCATE. We point out common differences and illustrate them with examples.