muzahid

• Mar 17th, 2005

 

Both the statement delete all row from the table EMP, but First one can not be rollback, second one will be rollback

rajeevsingh

• Mar 18th, 2005

 

out put will same but u can add some moredifference like1.delete can be rollback truncate cann.t be roleback.2.on delete trigger can be fire.but in truncate no trigger can be fire.3.delete is a dml statement while runcate is a dll statement .

Scott

• May 12th, 2005

 

The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.

Naveen Mishra

• Jul 18th, 2005

 

in delete operation , the database is checked fully ( regardless of the stmt ). This can take considerable amount of time for delete . but , in case of truncate , the database is not scanned , hence , it is much more fatser than delete.

murali

• Jul 29th, 2005

 

The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.  
 
As with DELETE, the definition of a table emptied using TRUNCATE TABLE remains in the database, along with its indexes and other associated objects. The DROP TABLE statement must be used to drop the definition of the table. 
 

Arpan Jain

• Aug 4th, 2005

 

Delete Command: 
1. It?s a DML Command 
2. Data can be rolled back. 
3. Its slower than Truncate command b?coz it logs each row deletion. 
4. With delete command trigger can be fire. 
Truncate Command: 
 
1. It?s a DML Command 
2. Data Can not be rolled back. 
3. Its is faster than delete b?coz it does not log rows. 
With Truncate command trigger can not be fire. 
 
both cases only the table data is removed, not the table structure. 

Rahul Tripathi

• Sep 20th, 2005

 

There is deference b/w truncate and delete that

1. Truncate is part of DDL. Delete is part of DML.

2. Truncate can't rollback but delete can.

3. Truncate is faster than delete.

4. truncate can't used where statement but delete can

Rahul Tripathi

• Sep 20th, 2005

 

There is deference b/w truncate and delete that

1. Truncate is part of DDL. Delete is part of DML.

2. Truncate can't rollback but delete can.

3. Truncate is faster than delete.

4. truncate can't used where statement but delete can

anupam

• Sep 28th, 2005

 

Truncate deletes the data/records from  the table whereas delete deletes the table.

S. Ramesh

• Sep 28th, 2005

 

Truncate and delete table doing the same operation, Truncate table delete all the rows same time,but delete table delete the table row by row. So truncate table comman work fase than delete command. Once you truncate the table you can't be rollbacked.

satish mullapudi

• Oct 15th, 2005

 

truncate by default is AUTOCOMMIT in nature. so whenever you truncate a table the results are permenantly deleted from the database. But in the case of delete , the rows can be rolled back using ROLLBACK  and the results can be restored in the database.

dinesh_maddisetty

• Jun 16th, 2006

 

   Both the truncate and delete commands delete the contents from the table,but there are some variations between them ,in the case of delete statement the items which are deleted can be rolled back,but its not the case with truncate, it will delete the items permanently.and the most important thing in the truncate is that the structure remains the same even though the contents are deleted .....

Manish

• Jun 24th, 2006

 

Hi,

One important difference that everybody missed is High Water Mark.

While using TRUNCATE the High Water Mark is set to zero and with DELETE it remains same.

High Water Mark is the highest mark upto which that table has ever reached.

vinod.km

 Profile Answers by vinod.km 

• May 14th, 2009

 

CREATE TABLE ABC
(
Col1 INT
)

INSERT INTO ABC VALUES (1)

BEGIN TRAN
TRUNCATE TABLE ABC
ROLLBACK TRAN
SELECT * FROM ABC

In this way, I am able to roll back truncate command. So in
SQL server we can Roll Back TRUNCATE Command.

yuvaevergreen

 Profile Answers by yuvaevergreen 

• Jun 14th, 2011

 

In Teradata,

Drop table and delete table are the options. delete table is preferred.