Difference between DELETE and TRUNCATE

When we can to remove the records from the table based on the condition we are using delete command. With the help of delete command we can delete the all record from the table or we can delete specific records from the table.

Truncate will remove the all records from the table. We can’t use condition in truncate. Once run the truncate all data from the table has been deleted.

Truncate

1.       It is faster than delete command.

I have created a table and inserting more than 2 lacks records.

create table Employee ( EmpID int identity(1,1), Ename varchar(MAX), Eaddress varchar(MAX), EMob varchar(MAX) ) DECLARE @c int set @c=1 while @c<255997 BEGIN insert into Employee values('Bagesh Kumar Singh','pune','8888802459') set @c=@c+1 END

Now I am trying to truncate the table. Let’s see how much time it is taking.

 

See here I truncate the table it took less then but same records I deleted using delete command it took 9 seconds.

2.       It removes the data and deallocating the memory.

 

Now I am inserting the 2.5 lacks records in the table

   

The size of the table is 13.703 MB.

Now I am truncating the table.

   

See now the size of the table is 0 MB. Now I am deleting records from the table let’s see the result

   

Still the Data space is showing 0.328 MM and the index space is 0.008MB.

3.       It removes the data from the table but the table structure remains same.

4.       We can’t truncate the table if the table is referred by a foreign key.

5.       It can’t roll back unless we used in a transaction.

6.        It is DDL command

7.       Truncate resets the identity field of the table.

 

EmpID is identity column.

If I truncate the table the Identity will be reset from 1 once again.

But in case of delete it is not reset. I have deleted the records from table and when I inserting the data the EmpID started from the last identity values.

 

Delete

1.       Delete remove records one at a time.

2.       It is slower than the truncate.

3.       Delete can use with or without where clause.

4.       Delete actives triggers.

5.       It can be roll back.

6.       It is DML command.

7.       Delete does not reset the identity of the table,

8.       It can’t releases the all memory spaces after deleting the records.