How do you differentiate between truncate and delete?
TRUNCATE and DELETE:
It also removes the rows from the specified location in the
query like DELETE command, but the only difference is :
1. rows once u truncated cnnot be ROLLBACKED like in DELETE. 2.
which ROLLBACK can be possible with delete. 3. Truncate is used for
HUGE databases or tables bcz it works efficiently. 4. DELETE will
work efficiently on small data(tables).
Now it is vident that the only difference between TRUNCATE and
DELETE is efficiency(i.e,Accessing Speed).
Annapurna.
TRUNCATE REMOVES ALL THE ROWS FROM THE TABLE AND CANNOT BE
Rollbacked, while delete removes all/specific rows from table and
can be rollbacked.
Also truncate resets the high water mark.
A common misconception is that they do the same thing. Not so.
In fact, there are many differences between the two. DELETE is a
logged operation on a per row basis. This means that the deletion
of each row gets logged and physically deleted. You can DELETE any
row that will not violate a constraint, while leaving the foreign
key or any other contraint in place. TRUNCATE is also a logged
operation, but in a different way. TRUNCATE logs the deallocation
of the data pages in which the data exists. The deallocation of
data pages means that your data rows still actually exist in the
data pages, but the extents have been marked as empty for reuse.
This is what makes TRUNCATE a faster operation to perform over
DELETE. You cannot TRUNCATE a table that has any foreign key
constraints. You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.
The difference between the two is that the truncate command is a
DDL operation and just moves the high water mark and produces a now
rollback. The delete command, on the other hand, is a DML
operation, which will produce a rollback and thus take longer to
complete.
Truncate can be rollbacked,, basically i see lot of aritcles
which says it can rollback,, but the truth is that we can rollback
after trancate ,, difference is that in truncate only deallocation
of the data page is done, but in delete the it is logged in deleted
table and is physically removed from data page,,,
Harishjan
The difference is with operation on memory because if you want
to have a place in memory for a later use you should use delete but
if you want clear memory you should use truncate - this function
clears the value and place where this value was stored.
For more detailed explanation about this topic please read the
following article.
http://www.sqlservercentral.com/articles/delete/61387/
Judy
Maxine
Blake
