Hello Friends Good Evening.. Today I am going to talk about
one more popular topic in SQL Server.
I am sure that this question would have been asked to you
during technical Interviews prior to joining a company.
I can count multiple differences between Truncate and Delete
statement in SQL Server.
But I am going to talk about only few main differences
between Truncate and Delete statement.
First Difference :- There is a myth around logging
operation of SQL Server for Truncate statement, that SQL Server doesn't log the truncate transaction. In other words we
cannot roll back a truncate transaction. Now coming to the difference,Truncate transactions are also logged but with minimal
logging, means SQL Server logs only unallocated pages, not for the each row of
the table.
In Delete transactions, each row is logged by the SQL Server
in the transaction Log.
Example :- Suppose we are truncating a table in a
transaction, then SQL Server remove all the rows from the data page(8 KB memory
chunk), and log the information of the data page that was
unallocated by freeing up the space of the data page. It means it does logging
for truncate transactions but on the page level not at the row level, on the other
hand in delete transaction, SQL Server delete one row and log the
information of that row in the transaction log.Hence, truncate is faster than a delete statement , and the
reason being is that truncate supports page level logging while delete supports
row level logging.So if you have a bigger data in the table then we should
prefer to use truncate statement.
Second Difference :- We cannot use truncate statement
if the intended table that is going to be truncated has some references in
terms of foreign key.But Delete statement supports this with the exception that
there should not be any data in the child table corresponding to the master
values in the primary table(foreign key table).
Example :- I have a table Employee with EID as
primary key in it and I have an another table named Conveyancedetail with
EID as foreign key, if I try to truncate table Employee irrespective of the fact
there is no child record in the Conveyancedetail table(Foreign key table), we
won’t be able to do that.
But in case of Delete statement we can do the same thing by
just removing the referenced data from the foreign key
table(Conveyancedetail )
create table employee(EID int identity(100,1) primary key,Name varchar(100))
insert into employee(Name) values('Raj')
insert into employee(Name) values('Vikas')
insert into employee(Name) values('Neeraj')
create table
Conveyancedetail(EID int
foreign key references employee(EID), conveyancetype varchar(100))
insert into Conveyancedetail
values(101,'Bike')
insert into
Conveyancedetail values(100,'Car')
insert into
Conveyancedetail values(102,'Scooty')
Now lets remove the data from Child table
Conveyancedetail.
Truncate table
Conveyancedetail
Now lets try to truncate table Employee.
truncate table
employee
We will get below error message.
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'employee' because it is being
referenced by a FOREIGN KEY constraint.
But if I try to use the same type of statement for delete it
will work.
delete from
Conveyancedetail
delete
from employee
Third Difference :- Truncate table reset the identity
value of the identity column while delete statement doesn't do that.
Example:- Lets take an example for this.
create table employee(EID int identity(100,1) primary key,Name varchar(100))
insert into employee(Name) values('Raj')
insert into employee(Name) values('Vikas')
insert into employee(Name) values('Neeraj')
Lets delete this table.
delete from employee
Now insert one more rows in employee table.
insert into employee(Name) values('Rachit')
Lets see what value has inserted
in the table for identity column, whether it is 100 or 103.
It would comes up as 103 as
delete statement doesn't reset the identity column.
If I use the same example for
Truncate table, lets see what comes up? It would be 100.
Two more differences are as
follows :-
1) As truncate does page level
logging hence it takes less transaction log space, while delete takes more
space because of row level logging.
2) We cannot use where clause in
truncate but can use where clause in delete.
No comments:
Post a Comment