472,123 Members | 1,334 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,123 software developers and data experts.

Differ between Truncate And Delete

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?

Jun 23 '07 #1
8 8099
5,058 Expert 4TB
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.
Jun 23 '07 #2
Thanks Alti..

Jun 25 '07 #3
delete only rows and space allocated by mysql
data can be roll backed again
it can be used with WHERE clause
Expand|Select|Wrap|Line Numbers
  1. DELETE TABLE table_name (for delete whole table)
  2. DELETE TABLE table_name WHERE column_name=condition (for delete particular row)
delete rows and space allocated by mysql
data cannot be roll backed again
it cann't be used with WHERE clause
Expand|Select|Wrap|Line Numbers
  1. TRUNCATE TABLE table_name
Jan 11 '14 #4
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.
Jan 15 '14 #5
77 64KB

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


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.
Sep 22 '20 #6
62 32bit
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.
Sep 25 '20 #7
10 Byte
Truncate is a DDL(Data Definition Language) command . It delete all the rows from a table .
Syntax : TRUNCATE Table Tablename;

Delete is a DML ( Data Manipulation Language) command . It is used to delete existing records in a table.
Syntax : DELETE table Tablename;
Feb 1 '23 #8
12 Byte
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.
Feb 1 '23 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by LineVoltageHalogen | last post: by
9 posts views Thread by Sumanth | last post: by
14 posts views Thread by Sala | last post: by
5 posts views Thread by Timothy Madden | last post: by
8 posts views Thread by orajit | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.