Difference Between DELETE and TRUNCATE Table in SQL Server

Total: 1 Average: 5

Many articles have been written to describe the difference between the SQL DELETE and SQL TRUNCATE statements. Moreover, it is one of the most common questions during job interviews. Both statements remove the data from the table. However, there are differences too.

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

CodingSight - Difference Between DELETE and TRUNCATE Table in SQL Server

The Summary of Differences Delete vs Truncate

Truncate Table statementDELETE statement
Deletes all records from the table. We cannot apply the WHERE clause to remove specific records.Removes all records and can apply the WHERE clause to delete specific records.
Does not fire the DELETE trigger.Executes the DELETE trigger.
Resets the identity value.Does not reset the identity value.
Is faster due to minimal usage of the transaction log.Is slower due to performing an initial table scan to count the number of rows to delete and removing rows one by one. The changes are logged in transaction logs.
Uses the row-level lock.Uses the table-level lock.
Cannot be used with indexed views.Can be used with indexed views.
Requires the ALTER TABLE permission.Requires the DELETE permission on the table.
The summary of differences between TRUNCATE Table and DELETE statements

For demonstration purposes, I have created a table named studentDB. There, I’ve made two tables, tblSchool and tblStudent, and inserted some records in both tables.

The following script 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]
GO

This script 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]
GO

The following script inserts data into the tblStudent table:

/*Insert Data in 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

The below script inserts data into the tblSchool table:

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

Now, let’s identify the differences between the statements.

Difference 1: Deleting the data

The DELETE command works to remove specific/all records from the table. The TRUNCATE statement deletes all data.

We’ll explore this scenario.

DELETE Statement

To remove specific records with DELETE, we can use the WHERE clause in the query. Suppose we want to delete some students from the tblstudent table, the student’s code is ST002.

Add the filter in the DELETE statement as follows:

Delete from  tblstudent where student_code='ST002'

This query will delete only one record from the table.

Once the record is deleted, run the select query to view the data:

Select * from tblstudent
The output of the query that deletes only one record from the table

TRUNCATE TABLE Statement

In the truncate table, adding the WHERE clause is impossible.

The following query removes all records from the tblStudent table:

Truncate table tblStudent

Difference 2: Triggers

When we run the DELETE command, the SQL Server invokes the DELETE triggers.

I have created a trigger named trgdeleteStudent on tblStudent. When we execute a DELETE statement on the tblstudent table, the trigger inserts a record in a tblDeletedStudent table.

The T-SQL code to create tbldeletedStudent is the following:

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 below T-SQL 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 below query to delete the record of the student ST0001:

delete from tblstudent where student_code='ST001'

Run the following query to verify:

select * from [dbo].[tblDelatedStudents]
The output of the query to delete the record of the student ST0001

As you can see in the above screenshot, a record has been added to the table.

Now, let us run the TRUNCATE TABLE statement to remove the data from the tblstudent table:

Truncate table [dbo].[tblDelatedStudents]

Verify the data by querying tblDeletedStudent:

select * from [dbo].[tblDelatedStudents]
The output of the query that runs the TRUNCATE TABLE statement to remove the data from the tblstudent table

As you can see, the records have not been inserted in the tblDeletedStudent table.Thus, the trgdeletestudent trigger did not fire.

Difference 3: Resetting the identity values

When we execute the DELETE command, the identity values won’t reset to the initial values. For the TRUNCATE table statement execution, the identity value will reset.

DELETE Statement

Run the below DELETE statement to delete the data from the tblStudent table:

delete from tblStudent where student_code='ST004'

Then, execute the following insert query to add records to the tblStudent the table:

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

Run the following query to view the data of tblStudent:

select * from [dbo].[tblStudent]
The output of the query execution  to add records to the tblStudent the table

The above image shows that the initial identity column value is incremented by one.

TRUNCATE TABLE

Run the below TRUNCATE TABLE statement to delete the data from the tblStudent table:

Truncate table [dbo].[tblStudents]

Once data is deleted, insert records 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

Run the SELECT query to view the data:

select * from [dbo].[tblStudent]
The output of the query to insert records in the table

As you can see in the above image, the identity value has been reset.

Difference 4: Permissions

To remove data using the DELETE statement, we must have the DELETE permission on the table.

To remove the data using the TRUNCATE TABLE statement, we require the ALTER TABLE permission.

DELETE Statement

I have created a user named testuser1 and assigned the DELETE permission on the tblStudent table.

We delete the record of the student with student_code=ST001:

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

Run the select query to view data:

The output of the query that deletes the record of the student with student_code=ST001

It deleted the record from the table.

TRUNCATE TABLE

Now, run the TRUNCATE TABLE to delete the data:

use StudentDB
go
truncate table tblstudent

The query returns the following error:

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

To rectify this, we must assign the ALTER TABLE permission.

Run the following query to grant access to testuser1 on the tblStudent table:

grant ALTER on tblstudent to testuser1

Re-run the truncate table statement:

use StudentDB
go
truncate table tblstudent

View the data from the table:

The output of the query to grant access to testuser1 on the tblStudent table

The data has been removed from the table.

Summary

This article explained the differences between the SQL DELETE statement and SQL TRUNCATE TABLE statement. We’ve defined all the essential features and illustrated them with examples.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.