Truncate VS Delete statements in SQL Server

What is the Difference between SQL Truncate and SQL Delete statements in SQL Server ?

Delete Command

• It is a DML command.
• It deletes the records based on conditions specified in the WHERE clause, and removes all the records if there is no Where clause.
• Identity column in a table is not reset.
• It removes records one at a time and keeps an entry in the transaction log for each deleted row.
• It supports the Triggers.

Truncate Command

• It is a DDL command.
• It does not support WHERE clause , Removes all the data all the time.
• Identity column in a table is reset to seed value.
• It removes the records by deallocating the data pages used to store the table’s data, and only the page deallocations are kept in the transaction log.
• It is Faster than DELETE as it locks entire table.
• It does not support Triggers.

 To delete a record from a table based on a condition

delete from EmployeeDetails where EmpId <=6 ;

To delete all records from a table using delete command

delete from EmployeeDetails ;

To delete all records from a table using truncate command

truncate table EmployeeDetails ;

Truncate and Delete with Rollback

Delete command can be rolled back, Lets look at an example.

Execute below scripts that is using a transaction block with roll back.

begin tran

delete from employeeDetails ;

select * from employeeDetails ;

rollback tran

select * from employeeDetails ;

Truncate command can also be rolled back, Lets look at an example.

Execute below scripts that is using transaction block with roll back.

begin tran

truncate table employeeDetails ;

select * from employeeDetails ;

rollback tran

select* from employeeDetails ;

TRUNCATE and DELETE with an Identity column

Here we have a table employeedetails . This table has identity on column- EmpId

 

You can check if table is using any an identity column, using SP_HELP command.

Sp_help Employeedetails ;

Delete command with resetting an identity value

When you run the DELETE statement without WHERE clause it deletes all the records from a table.

However, when a new record is inserted in a table , an identity value of column is increased from 1 to 2.

It means that the DELETE command does not reset the identity value of column .

Lets look at an example .

Following statement delete all the records from employeedetails table .

delete from employeedetails ;

Lets insert one new record in a table to see what value is generated for  an identity column for new record in a table.

insert into EmployeeDetails values ('Jhone M' ,'New York') ;

After inserting a new record in a table, you can see an identity value is increased by 1 that is 2, means it is does not reset even after deleting all records from a table.

Which means delete command does not reset value for an identity column so new value for new record is incremented by 1 on last seed value for an identity column that was in a table before deleting all records.

select * from employeedetails;

Truncate command with resetting an identity value

When you run TRUNCATE command, it removes all the records from a table.

However, when a new record is inserted in a table an identity value is increased from original seed value of column ,

Which means TRUNCATE resets the identity value to the original seed value of the column.

Lets look at an example. Here, we are using same table again with fresh new records , table has an identity on column Empid .

Lets delete all the records from a table using truncate command .

As you can see in below result set , all records are deleted from table.

Lets insert a new records in a table to see what values is assigned to an identity column for new records in a table.

insert into EmployeeDetails values ('Jhone M' ,'New York'),
('Anthony' ,'Boston'),
('Jacob' ,'Seattle'),
('Mark S jr.' ,'Seattle'),
('JackMa' ,'New york'),
('Donalad s' ,'Phoneix')

After inserting a record in a table ,you can see an identity value is increased from original seeds value of column that is 1, means TRUNCATE commands reset an identity value.

 

TRUNCATE and DELETE command with Trigger

DELETE commands support triggers

Lets look at an example , First we  create a log table that stores the deleted employees details.

Create table employeedetailslog(EmpId int, empname varchar(50),
City varchar(50))

Next , we create a trigger that is executed when delete operation is performed on employeedetails table.

Create trigger tr_emplog
on employeedetails
for delete
as

begin

insert into employeedetailslog
select d.empid,d.empname ,d.city from deleted d ;

end

Now, we delete all the records from a table in order to check whether a trigger is executed or not on executing the delete command.

delete from EmployeeDetails ;

Next , selects records from both base and log tables.

As you can see , all records are deleted from base table , and all these deleted records are inserted in log table by trigger .

Truncate commands does not support triggers

Lets insert the fresh records in employeedetails table , and remove all the logs from employeedetailslog table.

As you can see in below screenshot, we have a employeedetails which have six records and one log table ,which is empty.

select * from EmployeeDetails
select * from EmployeeDetailslog

 

 As you have already created a trigger on employeedetails table that inserts the deleted records into log tables.

Lets just truncate the employeedetails table.

truncate table EmployeeDetails

Lets check the log table in order to see whether all the truncated records are inserted in log table .

select * from EmployeeDetails
select * from EmployeeDetailslog

You can see, there are no records in EmployeeDetails as all the records are deleted by truncate command but these deleted records are not inserted either into Employeedetailslog table, which means truncate command does not support the trigger.

 

Also Read..

Copy or Delete files from window directory in SQL Server

SQL Server Import .CSV file in Database

SQL Server Bulk Insert(BCP)