GeekInterview.com
Series: Subject: Topic:
Question: 92 of 111

What is the difference between delete,drop and truncate?

Asked by: Interview Candidate | Asked on: Mar 10th, 2006
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

  
Login to rate this answer.
Unknown

Answered On : 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.

  
Login to rate this answer.
asimjamal

Answered On : Mar 23rd, 2006

View all answers by asimjamal

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.

  
Login to rate this answer.
Rammohan

Answered On : Mar 27th, 2006

Hi drop deleting the table and its structure from the data basedelete command used for deleting the records from the table,and it removing the table space which is allocated by the data basetruncate 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.ThanksRam

  
Login to rate this answer.
SUNIL

Answered On : Apr 17th, 2006

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

  
Login to rate this answer.
Ritesh Raj Singh

Answered On : Jun 27th, 2006

HiI dont think there is any possibility of rolling back a truncated transactionthrough rollback command ...if requires one can recover it through other wayseither 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 statementDrop 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
mohits

Answered On : Jul 26th, 2006

View all answers by mohits

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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
sneka

Answered On : Aug 9th, 2006

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

  
Login to rate this answer.
prachi

Answered On : 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.

  
Login to rate this answer.
PRASHANTKULAT

Answered On : Mar 7th, 2007

View all answers by PRASHANTKULAT

I dont know about Oracle. But acccording to sql serverDELETE 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.

  
Login to rate this answer.

                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

Yes  2 Users have rated as useful.
  
Login to rate this answer.
riteshratna

Answered On : May 11th, 2007

View all answers by riteshratna

Yes  1 User has rated as useful.
  
Login to rate this answer.

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.

  
Login to rate this answer.
dinesh kumar tiwary

Answered On : Nov 21st, 2007

Yes you can rollback the truncate command in SQL 2000

  
Login to rate this answer.
Pareshv

Answered On : Feb 11th, 2008

View all answers by Pareshv

It is possible to rollback the truncate operationIf you defined your truncate command within a begin and end transaction then it is possible to rollback the truncate operationCheck 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 sampletestbegin trantruncate table sampletestrollback

  
Login to rate this answer.
ketanbenegal

Answered On : Jun 2nd, 2008

View all answers by ketanbenegal

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.

  
Login to rate this answer.
hossain_camp4

Answered On : Jul 9th, 2009

View all answers by hossain_camp4

DELETEDelete 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
DROPDrop 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

  
Login to rate this answer.
gaurav_131

Answered On : Jul 23rd, 2009

View all answers by gaurav_131

DELETEWe 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.TRUNCATEWe 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. DROPThe 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.

  
Login to rate this answer.
yasho_d

Answered On : Jan 16th, 2010

View all answers by yasho_d

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

  
Login to rate this answer.
gauravt601

Answered On : Mar 25th, 2010

View all answers by gauravt601

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.

  
Login to rate this answer.
Prashant Sharma

Answered On : 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.

  
Login to rate this answer.
raviraj044

Answered On : Jan 25th, 2012

View all answers by raviraj044

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

  
Login to rate this answer.
mohitgyl

Answered On : Jan 26th, 2012

View all answers by mohitgyl

--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.

  
Login to rate this answer.
Aditi

Answered On : 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

  
Login to rate this answer.
raman sharma

Answered On : 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 ....

  
Login to rate this answer.
Hakkim

Answered On : 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.

  
Login to rate this answer.
karunakarsaroj

Answered On : May 24th, 2012

View all answers by karunakarsaroj

Truncate is much faster than delete. all same ans like above

  
Login to rate this answer.
lokesh

Answered On : 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

  
Login to rate this answer.
arunkumar

Answered On : 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.

  
Login to rate this answer.
Sheraz Baig

Answered On : 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Question Categories

Btrieve Interview Questions

Clipper Interview Questions

Database Admin Interview Questions

Firebird Interview Questions

Informix Interview Questions

Microsoft Access Interview Questions

MYSQL Interview Questions

PostgreSQL Interview Questions

Progress Interview Questions

SAP DB Interview Questions

SQL Interview Questions

SQL Server Interview Questions

Stored Procedures Interview Questions

Sybase Interview Questions

Teradata Interview Questions

Unify Interview Questions

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.