what is the difference between TRUNCATE and DELETE command
in SQL
- 23 Answers
- 58203 Views
- ICICI, Saama Tech, SkyTech, TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / chandu
Delete:
In delete statement we can use conditions
we can roll back
we can delete parent and child table rows also
it will activate in triggers
it can be slow compare to truncate.
Truncate:
In truncate statement we cant use conditons.
we cant roll back
we can delete child table rows only.
it will not activate in triggers.
it can be fast compare to delete.
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / dhanasekar.s
Truncate command executes faster then Delete command.
when you truncate the table we can't get back the data again
because data is removed from the memory.because ddl command
is auto commit.So we need not store any information about
deleted data in the memory.So it takes less time so faster.
But in the case of delete command is dml command.we can get
back(rollback) the data again.When you delete the data that
deleted data are stored in some where in memory that
information should be logged in the database log
information.From that information we can get the data again
easily.
So that (logging purpose) it takes time then truncate command.
It is my observation and my idea.If it is having any
mistakes please post the reason .
Thanks in Advance.
| Is This Answer Correct ? | 2 Yes | 1 No |
Answer / tripurari
truncate id ddl command .so it can't store data into redo log files, so it can't be rollback.
Delete is dml command so it contains data into log files so it may be rollback.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / padmapriya
1) Delete create log files when it is deleted and occupy
more space of memory
Whereas Truncate will not create any log files and hence
much faster than delete.
2) Delete can be roll back whereas Truncate is autocommit.
3) When there is foreign key constraint even though there
is no data in child table Truncate cannot be done. only
when the constraint is dropped truncate can be executed.
Whereas though we have foreign key constraints if the child
table is deleted, then parent table can be deleted.
| Is This Answer Correct ? | 2 Yes | 2 No |
Answer / bhupat
Truncate Statement:
1.Deletes all the rows from the table.
Delete Statement:
1.Delete a specific row.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / priya
Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.
TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
TRUNCATE is faster than DELETE.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sathya
I just wanna add a point ..
while truncating a table both the structure and the datas will be removed. where as in Delete structure of the table ramains only the data in the table will be deleted.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vijay rana
1.truncate is a DML command but Delete is a DDL Command
2.truncate is much faster then Delete
3.we can rollback delete but we cant rollback Truncate
4.we can not use where in truncate but we can use where with
delete
5.in case of truncate memory released immediately, but in
case of delete memory dosen release if we dont press commit.
| Is This Answer Correct ? | 2 Yes | 4 No |
Answer / vasanth
Truncate
1.Truncate Can be DDL Command.
2.it can be rollback.
3.we cann't use Where clause.
4.truncate=delete+commit so we cann't rollback.
5.truncate delete all record from the table.
6.truncate is a DDL command and cannot be rollbackand all
memory space is released back to server.
7.speed faster.
8.do not chcek constraint.
Delete
1.Delete Can be DML Command.
2.it can be rollback.
3.we can use Where clause.
4.delete=delete-- so we can be rollback.
5.delete delete record from the table.
6.truncate is a DDL command and can be rollback and all
memory space is not released back to server.
7.speed slow.
8. chcek constraint.
| Is This Answer Correct ? | 1 Yes | 3 No |
Answer / akshar jain
hi every one , i want to tell that truncate can also be
rolled back,i have used that , its my practical experience
if you want further clarifications write to me at
akshar.mysore@gmail.com
| Is This Answer Correct ? | 12 Yes | 18 No |
how to get the values if source table & target table having duplicate values that matches the lookup condition ?(i want all duplicate match)
What is the Symbol use for Inner Join
IS IT POSSIBLE TO CREATE REPORTS FROM DIFFERENT UNIVERSES IN ONE DOCUMENT?
deference between & and &&
types of receipts and detail desription about recipts and tables effected by receipts?
please explain about oracle
now iam working as an hr executive in hyderabad i would like to learn oracle hrms kindly suggest me some good institutes in hyderabd
Hi I am searching for best oracle Apps institute? Please tel me which is best institute in hyderabad I am working in developer 6i....
Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
Explain the use of table functions.
statement string in open is null or 0 length ORA-40735
When the procedures or packagess become invalidate
Oracle General 
