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.
The Summary of Differences Delete vs Truncate
| Truncate Table statement | DELETE 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. |
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
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 tblStudentDifference 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]
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]
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 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]
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:
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 testuser1Re-run the truncate table statement:
use StudentDB
go
truncate table tblstudent
View the data from the 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.
Tags: sql delete, sql server, t-sql, t-sql statements, truncate table Last modified: September 17, 2021
