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.
