What is the difference between delete,drop and truncate?

Showing Answers 1 - 30 of 30 Answers

DELETE: when u r using delete command in oracle the data will be deleted in the table. after that if u r using rollback the data in the table will be retrived.TRUNCATE: in the case of truncate, when u r using truncate what ever data in the table it will be deleted. after that if u r giving rollback also the data will not be retrived. but the structure of the table is available.DROP: in the case of DROP, if u r using drop the rollback command is not working and structure and data in the table also deleted. nothing willbe available about that table.EXAMPLE:DELETE: delete * from emp; "then the data inthe table will be deleted"after this u give ROLLBACKthen the data willbe retrieved. TRUNCATE: truncate table emp; " then the data inthe table willbe deleted" but the sturucture is available> if u type--- desc empthen it give the structure of that table.DROP: drop table emp:after that what ever command u r using on emp table it will give error. bcuz the table object emp is not available in database

  Was this answer useful?  Yes

Unknown

  • Mar 16th, 2006
 

The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database. Delete is not effective when you use it to remove all data from a table, because it takes up storeage spaces due to unclear memories. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and only use it when you need to remove all data from a table. The drop statement will result in completely removing the table from the database. Note: Statements above are made with the assumption that a commit is has been executed. Once a commit statement is executed (commit is final). Therefore, rollback a transaction will not work if commit statement is executed.

  Was this answer useful?  Yes

asimjamal

  • Mar 23rd, 2006
 

Delete command: It actually removes the data permanently from the table with user specified conditions until you commit your work.It you want to save ,you could rollback the command. It will also create the space in the tablespace which in turn in a datafile.Drop command : It will remove both the structure and data in the specified table and could not be rolled back as it is a DDL command.Truncate : It will remove full data from the table without any user specified condition. It cannot be rolled back.

  Was this answer useful?  Yes

Rammohan

  • Mar 27th, 2006
 

Hi

drop deleting the table and its structure from the data base

delete command used for deleting the records from the table,and it removing the table space which is allocated by the data base

truncate is also delete the records but it is not delete the table space which is created by the data base.

note:Here For every table in the DB the DB allocated some default space for that table.

Thanks

Ram

  Was this answer useful?  Yes

SUNIL

  • Apr 17th, 2006
 

THE Ariticle is wrong even in Truncate the transaction can be rolled back

  Was this answer useful?  Yes

Ritesh Raj Singh

  • Jun 27th, 2006
 

Hi

I dont think there is any possibility of rolling back a truncated transaction

through rollback command ...if requires one can recover it through other ways

either via a point in time recovery using incomlete recovery...but as per oracle syntax rollback is concerned no body can recover a truncated tables data through rollback command.

See Delete Deletes the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback the tables data after delete as well. one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim by delete.when you deletes table data indexes are not dropped.Its a DML statement

Drop drops all tables with its data structure idexes and integrity constraints.

Truncate command removes all the data from table and drops all integrity constraints its an DDL Statement and resets the HWM but you can't rollback a tables data after truncate and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.

mohits

  • Jul 26th, 2006
 

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Flashback complete.

sneka

  • Aug 9th, 2006
 

This article will be very useful for me. It 's a good explanation for sql. thank u

  Was this answer useful?  Yes

prachi

  • Aug 23rd, 2006
 

Delete : A DML Statement creates a undo log . deletes record from table . Can be rolled back.

Truncate : A DDL it actually sets the high water mark level of the table back to zero (depends on MINEXTENTS too) , hence no one can read the table records.The  commend get itself issues a commit as it is  DDL command hence can not be rolled back . Unlike delete truncate can not be selective . Table defination remains as such. It is fast as no logs are maintained.

DROP:drops a table completelty (with table structure)can not  be rolled back . can be cascaded.

  Was this answer useful?  Yes


I dont know about Oracle. But acccording to sql server

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

  Was this answer useful?  Yes

                delete:
                           removes a sigle row from the table;
                truncate:
                           removes all rows from the table,the table view exist
               drop:
                        completely remove table from the database,the table viw not exist

riteshratna

  • May 11th, 2007
 

Delete
Delete remove the Data only, the Table structure remains intact.

This is a DML Statement
Rollback possible
No Commit is performed neither before nor after. (Because it is a DML Statement).
They take locks on rows,
They generate redo (lots of it)
They require segments in the UNDO tablespace.
A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
A truncate does not move the High Water Mark of the table back to zero, it retains itÂ’s original position.
Delete deletes the specific rows filtered by where statement. and log is maintained for it.
It can activate the triggers.

Truncate
Truncate remove the Data only, the Table structure remains intact.
This is a DDL Statement
Rollback not possible (Except in SQL 2005)
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)
No row-level locks are taken.
No redo or rollback is generated.
They do not require segments in the UNDO tablespace.
All extents bar the initial are de-allocated from the table
A truncate moves the High Water Mark of the table back to zero
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.

Drop
Drop permanently removes both the Data as well as the Table Structure.
This is a DDL Statement
Rollback not possible
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)
No row-level locks are taken.
No redo or rollback is generated.
They do not require segments in the UNDO tablespace.
All extents bar the initial are de-allocated from the table
A truncate moves the High Water Mark of the table back to zero
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.

delete:1)It uses where clause.
            2)It makes an entry in the transaction log each time it deletes a row,hence
                is time taking.

drop: It deletes the structure as well as the data.

truncate: 1)It uses no where clause.
               2)It makes use of less system and tansaction log,hence is faster compared  to delete.
                3)you can't delete the structure.

  Was this answer useful?  Yes

dinesh kumar tiwary

  • Nov 21st, 2007
 

Yes you can rollback the truncate command in SQL 2000

  Was this answer useful?  Yes

Pareshv

  • Feb 11th, 2008
 

It is possible to rollback the truncate operation

If you defined your truncate command within a begin and end transaction then it is possible to rollback the truncate operation

Check the below code :

create table sampletest(id int identity(1,1),Name varchar(50))

insert into sampletest(Name)values('Paresh')
insert into sampletest(Name)values('Naresh')
insert into sampletest(Name)values('Suresh')
insert into sampletest(Name)values('Ramesh')
insert into sampletest(Name)values('Kalesh')

select * from sampletest

begin tran

truncate table sampletest

rollback

  Was this answer useful?  Yes

Delete: DML statement, can rollback and specify where clause.
Truncate: DDL statement, can't rollback, cannot specify where clause, retains table structure, deletes all rows.
Drop: DDL statement, can't rollback, cannot specify where clause, deletes table structure, deletes all rows.

  Was this answer useful?  Yes

DELETE

Delete keyword is used to delete some row from table with
conditionally or all rows from table.
Like example:
SELECT COUNT(*) FROM
employees;
21


DELETE FROM employees WHERE job_id='it_back';
some rows are deleted from employees where job_id is it_back.


SELECT COUNT(*) FROM employees;
14


ROLLBACK;


SELECT COUNT(*) FROM employees;
21


rollback is applicable on delete keyword.
but if we use commit after 2no statement, then we will get after execute select count(*) from employees - 14

DROP

Drop is a keyword which is used to drop a table from database. it
makes free the storage of table.


DROP TABLE employees;


SELECT * FROM employees;
it will be show a error.

ROLLBACK;

SELECT * FROM employees;
it will
show error because drop statement is completed auto commit;


TRUNCATE


Truncate is used to delete all rows from table. It does not use condition to delete rows from table.
It is also completed auto commit statement;


TRUNCATE TABLE employees;
SELECT *
FROM employees;
No row is selected


ROLLBACK;
SELECT * FROM employees;
No row is selected

  Was this answer useful?  Yes

gaurav_131

  • Jul 23rd, 2009
 

DELETE

We can use the DELETE command to remove rows from a table. We must specify a WHERE clause in our DELETE statements so only some rows will be removed.If we do not do that all rows will be removed and that something we most certainly never want. DELETE operations will cause all DELETE triggers on the table to fire.

TRUNCATE

We can use the TRUNCATE statement to remove all rows from a table. A very important thing to note is that the TRUNCATE operation cannot be rolled back and no triggers are fired. 

DROP

The DROP statement removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. The operation cannot be rolled back and no triggers will be fired.

  Was this answer useful?  Yes

yasho_d

  • Jan 16th, 2010
 

Truncate can be rolled back. DDL command.
Delete cannot roll back. DML command.
Drop cannot roll back. DDL command.

  Was this answer useful?  Yes

gauravt601

  • Mar 25th, 2010
 

DELETE is used to delete all records in the table. It is a DML command while DROP deletes the structure of the table. TRUNCATE is much faster than DROP and it cannot ROLLBACK.

  Was this answer useful?  Yes

Prashant Sharma

  • Jul 17th, 2011
 

(1)Drop and Truncate both are DDL (Data Definition Language) commands while Delete is a DML(Data Manipulation language) command.
(2)Drop and Truncate commands can't be rolled back once implemented while Delete command can be rolled back.
(3)Drop command deletes all the things belonging to the table (schema, metadata,complete table structure and data) while Truncate removes all the rows from the table .
(4)Delete command removes the rows specified in the where condition. If NO WHERE condition is specified then all the rows are removed from the table . But, the operation can be rolled back if we want to undo the changes. If we really want to keep the changes we have done then commit is required.

  Was this answer useful?  Yes

raviraj044

  • Jan 25th, 2012
 

Delete and Truncate Both Can be Rolled back

Code
  1. CREATE TABLE sampletest(id int identity(1,1),Name varchar(50))

  2.  

  3. INSERT INTO sampletest(Name)VALUES(Paresh)

  4. INSERT INTO sampletest(Name)VALUES(Naresh)

  5. INSERT INTO sampletest(Name)VALUES(Suresh)

  6. INSERT INTO sampletest(Name)VALUES(Ramesh)

  7. INSERT INTO sampletest(Name)VALUES(Kalesh)

  8.  

  9. SELECT * FROM sampletest1

  10.  

  11. SELECT * INTO sampletest1 FROM sampletest

  12.  

  13. begin tran

  14.  

  15. DELETE sampletest1

  16.  

  17. rollback

  Was this answer useful?  Yes

mohitgyl

  • Jan 26th, 2012
 

--Delete is a DML Operation while Drop/Truncate are DDL operation
--Delete can be rolled back but Drop/Truncate can not be rolled back
--On Delete, transaction logs are written but Drop/Truncate does not write transaction logs
--Delete operation is applied on the data inside a table with where condition (may or may not), Truncate operation is used to remove ALL the data from a table (no where condition is allowed) and Drop is used to drop a Database Object like Table,view, Store Procedure etc.

  Was this answer useful?  Yes

Aditi

  • Feb 8th, 2012
 

Truncate table command can be rolled back.
Try this:
Create a table named as test
insert some records into this table
After insertion run select * From test to check insertion of records in the table
then execute these lines
begin tran
truncate table test
execute - select * From test, to check deletion of records
the execute this:
rollback

  Was this answer useful?  Yes

raman sharma

  • Mar 2nd, 2012
 

first of all delete is DML command and truncate is a DDL command requirement is DML command does rollback but DDL command does not rollback ....

  Was this answer useful?  Yes

Hakkim

  • May 22nd, 2012
 

Drop : Deletes both Table Structure and the Data
Truncate : Deletes only Data and it performs automatic Commit
Delete : Deletes only Data and Doesn't performs automatic Commit.

  Was this answer useful?  Yes

lokesh

  • Jun 5th, 2012
 

In truncate all the data will be deleted whereas in delete also same but the difference in delete we again reback the data by using flashback (without use commit) in truncate it is not possible while drop the total table is drop

  Was this answer useful?  Yes

arunkumar

  • Jun 18th, 2012
 

Delete: delete a single row or multiple row
it is DML operation so rollback is enable.
Truncate: delete from all record in table. commit or rollback is not using. trigger will not fired.but column will there
Drop : delete a table structure. commit or rollback is not working. trigger also not working.

  Was this answer useful?  Yes

Sheraz Baig

  • Nov 21st, 2013
 

Delete is DML statement which delete the record/s and can be Rolled Back.
Truncate does same work but because its a DDL statement so cannot be Rolled Back.
Drop is DDL statement which delete the Table structure witht he data, Cannot be Rolled Back.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions