What is difference between TRUNCATE & DELETE?
Answer Posted / ameya aloni
TRUNCATE:
1. Faster - does its work in single execution by
deallocating the data pages used by the table and reducing
the resource overhead of logging the deletions, as well as
the number of locks acquired.
2. Is DDL command
3. Removes all data
4. Does not make entries in a LOG file
3. Can't be rolled-back
4. Can't filter using WHERE clause
5. Can't call DML triggers
6. Can't ensure data consistency in case of foreign-key
references
7. Resets the IDENTITY back to the SEED
DELETE:
1. Slower - does its work by deleting rows one at a time,
logging each row in the transaction log, maintaining log
sequence number (LSN) information and consuming more
database resources and locks.
2. Is DML command
3. Removes data row-by-row
4. Makes entry per row in a LOG file
3. Can use COMMIT or ROLLBACK
4. Can filter using WHERE clause
5. Can call DML triggers
6. Ensures data consistency in case of foreign-key references
7. Does not reset the IDENTITY
Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
How to read xml file in oracle pl sql?
Compare sql & pl/sql
Why use stored procedures?
Is sql workbench free?
What is the main difference between sql and pl/sql?
What is attribute indicator in pl sql?
What is difference between table and view?
How do I copy a table in sql?
What is an invalid partition table?
what is the difference between join and union? : Sql dba
What is bulk compiling in pl/sql.?
What is assignment operator in pl sql?
What is difference between rank () row_number () and dense_rank () in sql?
How many types of index are there?
What is #table in sql?