The difference between DELETE and TRUNCATE statements is one of the most common questions in job interviews. Both statements can DELETE data from the table. However, there are differences.
This paper will focus on these differences and illustrate them with examples.
TRUNCATE |
DELETE |
Delete all records from the table. We cannot use WHERE to delete specific records |
Delete all records and use WHERE to delete specific records. |
The DELETE trigger is not triggered. |
Trigger DELETE trigger |
Reset identity column |
Do not reset identity columns |
Because there are few logs, it is faster. |
It is slower because a table scan is performed to calculate the number of rows to delete and delete rows one by one. Changes are recorded in the transaction log. |
Use row level lock |
Use table lock |
ALTER TABLE permission is required |
DELETE} permission is required for the table |
For demonstration purposes, I created a table called studentDB. In addition, two tables, tblSchool and tblStudent, were created and some records were inserted into the 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] GO
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] GO
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
Insert data into the tblSchool table:
insert into [dbo].[tblSchool] ([school_name], [city]) values ('Nalanda School','Mehsana'), ('Sarvajanik School','Mehsana')
Now, let's look at the differences between the two.
1. Delete data
The DELETE command deletes specific / all records from the table. TRUNCATE statement deletes all data.
-
DELETE
To DELETE a specific record using DELETE, you can use the WHERE clause in the query. Suppose we want to DELETE some students from the tblstudent table whose code is ST002. Add the following conditions to the DELETE statement:
Delete from tblstudent where student_code='ST002'
After execution, only one record in the table will be deleted. Once the record is deleted, run the select query to view the data:
-
TRUNCATE
In truncate, it is impossible to add a WHERE clause. The following query deletes all records in the tblStudent table:
Truncate table tblStudent
2. Trigger
When we run the DELETE statement, SQL will call the DELETE trigger.
I have created a trigger named 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 tbldeleted student 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 a 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 the record of student ST0001:
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 the tbldeletedstudents table.
Now, let's run the TRUNCATE TABLE statement to delete the data in the tblstudent table:
Truncate table [dbo].[tblDelatedStudents]
Verify the data by querying tblDeletedStudent:
select * from [dbo].[tblDelatedStudents]
As you can see, there are no records inserted in the tblDeletedStudent table. Therefore, the trgdeletestudent trigger was not triggered.
3. Reset identification 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
Execute the DELETE statement to DELETE the data in the tblStudent table:
delete from tblStudent where student_code='ST004'
Then, execute the following insert statement to add records to the table tblStudent:
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 the data of tblStudent:
select * from [dbo].[tblStudent]
The initial identification column value plus 1 is displayed here.
-
TRUNCATE
Execute the following TRUNCATE statement to delete the data in the tblStudent table:
Truncate table [dbo].[tblStudents]
After deleting data, 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
Execute the following query command to view the data of tblStudent:
select * from [dbo].[tblStudent]
To sum up, the identity column has been reset.
4. Authority
To DELETE data using the DELETE statement, you must have DELETE permission on the table. To DELETE data using TRUNCATE TABLE statement, we need ALTER TABLE permission.
-
DELETE
I have created a user named testuser1 and assigned delete permission on the tblStudent table.
We delete the student_ Student records with code = st001:
use StudentDB go delete from tblstudent where student_code='ST001'
Then view the data of tblStudent:
It did delete the record from the table.
-
TRUNCATE
Now run TRUNCATE to delete the data:
use StudentDB go truncate table tblstudent
The query returned 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 correct this problem, we must assign ALTER TABLE permission. Execute the following query to grant access to the tblStudent table:
grant ALTER on tblstudent to testuser1
Execute truncate again:
use StudentDB
go
truncate table tblstudent
The results are as follows:
The data in the table is deleted.
This article explains the difference between DELETE statement and TRUNCATE statement. We point out the common differences and illustrate them with examples.
Original link: https://codingsight.com/difference-between-delete-and-truncate-table-in-sql-server/