What is the difference among "dropping a table”,” truncating a table" and deleting all records" from a table?

Questions by Beena   answers by Beena

Showing Answers 1 - 25 of 25 Answers

 Dropping table the structure of table as will as row of table .

but truncate table delete all the row of table but structure of table remain same we can apply  where condition on it . delete work like truncate the table but we can apply where condition on it.  Truncate can't roll back delete can. 

  Was this answer useful?  Yes

High Octane

  • Nov 10th, 2005
 

Here is a more complete answer: "dropping a table? removes the table definition from the schema of the database and, of course, all the data? truncating a table" internally within the database 'marks' the table as empty and this is imporant: the deleted records are not recorded in the transaction log."deleting all records" removes the records from the table and records each deletion in the transaction log

High Octane

  • Nov 10th, 2005
 

Here is a more complete answer: "dropping a table? removes the table definition from the schema of the database and, of course, all the data? truncating a table" internally within the database 'marks' the table as empty and this is imporant: the deleted records are not recorded in the transaction log."deleting all records" removes the records from the table and records each deletion in the transaction log

  Was this answer useful?  Yes

starmile

  • Jan 2nd, 2006
 

Dropping table deletes table definition and  records both

Delete commannd deletes all records but records all details in Transaction log file. 

Truncate Table provides some special features in addition to delete all records from table . like Delete trigger not fired when it executes, Reset table identity. only page deallocation details send to Transeaction logs

  Was this answer useful?  Yes

Prasad Meduri

  • Feb 13th, 2006
 

DROP <Table>: Drops the table records and structure from the DB schema. Nothing else is leftout. Can't rollback. 

Truncate <Table>: Clears the records from the table but the table structure still remains. Can't roll back.

delete from <table>: deletes the rows based on the query or will delete all the rows if no "where" clause is given. Can roll back i.e deleted records can restored.

In the first two cases transaction log is not done, but with delete transaction log is created. So it consumes more time compared to the above two.

Correct me if i'm wrong

  Was this answer useful?  Yes

aruna

  • Apr 3rd, 2006
 

Dropping tje table means we are deleting both structure & data

Deleting the table means just we are deleting the data but not structure

truncate also same as delete.

  Was this answer useful?  Yes

Dropping a table will drop the table schema from the database, there wont be any more record of that table.Deleting a row will delete row from certain table with entry in log file for every row deleted, which we can rollback., it will not reset identity value of identity columnTruncating a table will delete all the rows from the table, and will make only one entry in log file for deleting all the row, THAT ALSO WE CAN ROLL BACK. Also it will reset Identity value of the identity column.DIFFERENCE BETWEEN DELETE AND ROLLBACK IS THAT : in delete it will make one entry for each row to be deleted. while truncate will make only one entry for all the rows. in delete it will free all the datapages by deleting the contents of the data pages. while in truncate it will delete the pointer to those datapages for all deleted rows.Also we can not use truncate if we have column in that table, which is referenced in other table. But we can use delete if we provided cascade options while creating tables.

swathi

  • Apr 10th, 2006
 

can we apply where conditon on delete and truncate table.

  Was this answer useful?  Yes

D.Aringan

  • Aug 23rd, 2006
 

Truncating a table is just remove all the records from the table.'where'  clause is not used in truncating a table and also we cannot rollback the table.In delete option we can use WHERE clause to delete the rows and also we can rollback the deleted table.

  Was this answer useful?  Yes

Deepek Bhandari

  • Mar 30th, 2007
 

Drop Table: Its delete all the column or selected column of a table. and after using drop command we can not rollback.

Delete: With delete we can delete one or more row of a table but we can rollback the method and retrive all the data of a table after deleting.

Turncate: using turncate we can not delete selected row, its delete the whole table and remain only structure of a table, and after rollback we can retrive the structure of deleted table.

  Was this answer useful?  Yes

abhineet

  • Apr 17th, 2007
 

In the Drop table, this is DDL command to delete all the record from the table but some transaction log file will occur and it may include WHERE condition.

In the truncate table, this is a DML command to drop all the record without any WHERE condition in the table, it will not generate any transaction log file.

  Was this answer useful?  Yes

bobby

  • Oct 14th, 2007
 

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You can't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause

  Was this answer useful?  Yes

Delete is a DML Statement
Truncate is a DDL Statement

Delete can be rolledback
Truncate cannot be rolledback

The memory occupied by the table will be released in Truncate.
That is Not the case with Delete

Truncate cannot use a where clause
Delete can use a where clause

Truncate is faster than Delete.


  Was this answer useful?  Yes


Truncate:
Deletes all the record from the table.
keeps no entry in log file.so rollback is not possible
fast in operation than delete.
Its a DDL statement
Reset the identity column value to 1 (specific to sql server)

Delete:
Deletes all or some record from the table.
keeps an entry in log file.so rollback is possible
slow in operation than truncate.
Its a DML statement
do not reset the identity column

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions