Differ between Truncate And Delete

[savanm]

Hi all...

Wt's the difference between truncate and delete Anything other than this

Truncate is a DDL statment
Delete is a DML statment

Truncate, drops the table then recreate it
Delete,It delete the all the datas...

If any major difference?...Wt's the absolute use?

Regards
nAvAs.M

[Atli]

The biggest difference is what you said, TRUNCATE drops the table and then re-creates it. This also means all AUTO_INCREMENT fields are reset.

Using DELETE the table is not dropped, only the data is removed. So the AUTO_INCREMENT fields are not reset, which means they continue counting where they left of before the delete.

[savanm]

Thanks Alti..

nAvAs.M

[gurumoorthi]

Delete:
delete only rows and space allocated by mysql
data can be roll backed again
it can be used with WHERE clause
Syntax:

DELETE TABLE table_name (for delete whole table)
DELETE TABLE table_name WHERE column_name=condition (for delete particular row)

Truncate:
delete rows and space allocated by mysql
data cannot be roll backed again
it cann't be used with WHERE clause
Syntax:

TRUNCATE TABLE table_name

[albertdenim]

Commit and Rollback defined the major difference between Truncate and Delete.

Delete: Delete command removes row from a table. After delete operation we can use commit and rollback to make the change permanent or undo it.

Truncate: Truncate removes all rows from a table. After truncate operation we can not use commit and rollback to make the change permanent or undo it.

[Sherin]

DELETE

Basically, it is a Data Manipulation Language Command (DML). It is use to delete the one or more tuples of a table. With the help of “DELETE” command we can either delete all the rows in one go or can delete row one by one.

DELETE is a DML command.
DELETE is executed using a row lock, each row in the table is locked for deletion.
We can use where clause with DELETE to filter & delete specific records.
The DELETE command is used to remove rows from a table based on WHERE condition.
It maintains the log, so it slower than TRUNCATE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row

TRUNCATE

It is also a Data Definition Language Command (DDL). It is use to delete all the rows of a relation (table) in one go. With the help of “TRUNCATE” command we can’t delete the single row as here WHERE clause is not used. By using this command the existence of all the rows of the table is lost.

TRUNCATE is a DDL command
TRUNCATE is executed using a table lock and the whole table is locked to remove all records.
We cannot use the WHERE clause with TRUNCATE.
TRUNCATE removes all rows from a table.
Minimal logging in the transaction log, so it is faster performance-wise.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

[Naheedmir]

The difference between DELETE and TRUNCATE are that that DELETE statement lock each row in the table for deletion whereas, TRUNCATE TABLE locks the table but not each row. Similarly, Rollback is not possible in TRUNCATE but not in DELETE.

[tmudgal16]

Truncate is a DDL(Data Definition Language) command . It delete all the rows from a table .
Syntax : TRUNCATE Table Tablename;
Example : TRUNCATE TABLE STUDENT;

Delete is a DML ( Data Manipulation Language) command . It is used to delete existing records in a table.
Syntax : DELETE table Tablename;
Example : DELETE TABLE STUDENT;

[Techii1120]

1) Delete command is used to delete one or more rows while truncate command is used to delete all rows.
2) In delete command where clause maybe used while in truncate command where command is not used.
3) In delete command tuple is locked while removing the data while in truncate command data page is locked.
4) Delete command is slower while truncate command is faster.
5) Delete can be used with indexed views while truncate command cannot be used with indexed views.
6) Delete command occupies more transaction space while truncate command occupy less transaction space.