469,342 Members | 6,104 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

Differ between Truncate And Delete

savanm
85
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
Jun 23 '07 #1
6 3207
Atli
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
savanm
85
Thanks Alti..

nAvAs.M
Jun 25 '07 #3
Delete:
delete only rows and space allocated by mysql
data can be roll backed again
it can be used with WHERE clause
Syntax:
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)
Truncate:
delete rows and space allocated by mysql
data cannot be roll backed again
it cann't be used with WHERE clause
Syntax:
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
Sherin
77 64KB
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.
Sep 22 '20 #6
Naheedmir
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

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.