What is the difference between DELETE and TRUNCATE?

Answers were Sorted based on User's Feedback



What is the difference between DELETE and TRUNCATE?..

Answer / babloo

when truncate command is issued, all the rows from the
table are deleted and the memory space occupied by the
table in the tablespace is also released whereas when
delete command is issued, only all the rows are deleted
fronm the table

Is This Answer Correct ?    28 Yes 0 No

What is the difference between DELETE and TRUNCATE?..

Answer / protyusha banerjee dawn

Whenever we delete records then they are stored in data
dictionary and can be retrieved later ie by roll back.
but when we truncate then the memory used by the records
which is to be truncated is released and it cannot be
rolled back.

Is This Answer Correct ?    24 Yes 5 No

What is the difference between DELETE and TRUNCATE?..

Answer / brajendra arzare

Deletes perform normal DML. That is, they take locks on
rows, they generate redo and they require segments in the
UNDO tablespace. Deletes clear records out of blocks
carefully. If a mistake is made a rollback can be issued to
restore the records prior to a commit.
Truncates are DDL and truncate moves the High Water Mark
of the table back to zero. No row-level locks are taken, no
redo or rollback is generated.so they have the same effect
as a delete, but without all the overhead. Just one slight
problem: a truncate is a DDL command, so you can't roll it
back if you decide you made a mistake. (It's also true that
you can't selectively truncate -no "WHERE" clause is
permitted, unlike with deletes, of course).

Is This Answer Correct ?    13 Yes 1 No

What is the difference between DELETE and TRUNCATE?..

Answer / satyajit patel

DELETE: DML, Manual Commit, Till not committed can be
rollback, can be applied for both row level as well as
table level, can be used in trigger, doesn't release memory.

TRUNCATE: DDL, auto commit, cann't rollback, applied table
level, cann't be used in trigger, releases memory.

In both cases structure remains.

Is This Answer Correct ?    14 Yes 2 No

What is the difference between DELETE and TRUNCATE?..

Answer / manivasan.s

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.

You can DELETE any row that will not violate a constraint,
while leaving the foreign key or any other contraint in place.

Is This Answer Correct ?    10 Yes 1 No

What is the difference between DELETE and TRUNCATE?..

Answer / p.rajasekar

Delete:
1.It is DML staement.
2.We can rollback the Data
3.We can apply Where condition.
4.Database triggers will fire on DELETE.
5.High water mark Model is not applied
Truncate:
1.It is a DDL statement
2.We cannot rollback
3.We can not apply Where condition
4.Database trigger will not fire
5.High water mark Model is applied

Is This Answer Correct ?    9 Yes 1 No

What is the difference between DELETE and TRUNCATE?..

Answer / althaf

the delete command log all the stmts in the transaction log
and the truncate command dont log the stmts.

Is This Answer Correct ?    8 Yes 2 No

What is the difference between DELETE and TRUNCATE?..

Answer / kalyana chakravarthy

Delete is associated with rollback segments like U can undo
it before committing and U have a choice of deleting a
specific row or rows where as

Truncate is not associated with rollback segments ie U cant
undo it and U cant delete a row/rows of ur choice

Is This Answer Correct ?    6 Yes 1 No

What is the difference between DELETE and TRUNCATE?..

Answer / gourvendra singh

Truncate will remove the watermark from the table, but the
delete will not.

Is This Answer Correct ?    6 Yes 4 No

What is the difference between DELETE and TRUNCATE?..

Answer / anitha

If we are using the delete command means the content of the
table will be deleted.but we can recover that data by using
the roll back query in my sql.
But this one is not possible we can use truncate.so some
times of our carelessness we can loss some valuable and
sensitive datas.
so make sure of that before using truncate...

Is This Answer Correct ?    3 Yes 1 No

Post New Answer

More SQL PLSQL Interview Questions

Why do we use sql constraints?

0 Answers  


What is an Exception ? What are types of Exception ?

2 Answers  


Is sql workbench free?

0 Answers  


How many times can we commit in a loop?

0 Answers  


What is difference between joins and union?

0 Answers  






how to Update table Sales_summary with max(sales) data from table sales_dataTable 1. sales_data table Table 2. Sales_summary Region sales Region sales N 500 N 0 N 800 W 0 N 600 W 899 W 458 W 900 I want the Sales_summary After Update like this Region Sales N 800 W 900

8 Answers  


Explain the commit statement.

0 Answers  


What is a temp table?

0 Answers  


What is left join in postgresql?

0 Answers  


What kind of join is join?

0 Answers  


Which command is used to delete a trigger?

0 Answers  


What is mutating table error?

0 Answers  


Categories