Home > Software > Data-Warehouse > Informatica
Interview Questions   Tutorials   Discussions   Programs   

Informatica - What is the difference between truncate and delete in which situation you use delete and truncate in real time?




695
views
asked marvit September 20, 2014 07:59 AM  

What is the difference between truncate and delete in which situation you use delete and truncate in real time?


           

1 Answers



 
answered By vishnoiprem   0  
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 constraint 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 constraints, TRUNCATE the
table, and reapply the constraints.

TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity
columns.  After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1.  DELETE will not do this.  In the same scenario, if you DELETE d your rows, when inserting a new row into the empty table, the
identity column will have a value of 265.
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert