What is difference between TRUNCATE & DELETE?
- 16 Answers
- 39777 Views
- Ahn Infotech, CitiGroup, ICICI, PreVator, Saama Tech, SkyTech, TCS, I also Faced
- E-Mail Answers
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
Explain isolation levels. : Transact sql
How do I save a stored procedure?
Is pl sql better than sql?
What is numeric function sql?
what is an alias command? : Sql dba
What is %rowtype in pl sql?
How to create your own reports in sql developer?
Why functions are used in sql?
Can you load data into multiple tables at once? : aql loader
What is a recursive join sql?
how can you see all indexes defined for a table? : Sql dba
How do I get sql certification?
Can you inner join the same table?
What is data type in database?
What are different types of sql?
