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:

Execute the following query command to verify:

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:

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:

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

Execute the following query command to view the data of 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:

After deleting data, insert records in the table:

Execute the following query command to view the data of 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:

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:

Execute truncate again:

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/

​