473,728 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Differ between Truncate And Delete

85 New Member
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?...W t's the absolute use?

Jun 23 '07 #1
7 9109
5,058 Recognized Expert Expert
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
85 New Member
Thanks Alti..

Jun 25 '07 #3
3 New Member
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
1 New Member
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 New Member

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 New Member
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 New Member
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

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

Similar topics

by: martin | last post by:
Hi, We have a heavily used production server and a table which logs every hit on a web site. This table has grown large over time and we want to clear it down as efficiently as possible. We would like to issue a truncate table statement, but with millions of rows we are a bit wary of how this will affect server performance. The alternative is to delete is stages using rowcount but of course this will generate a large amount of logging....
by: LineVoltageHalogen | last post by:
Greeting All, I have a stored proc that dynamically truncates all the tables in my databases. I use a cursor and some dynamic sql for this: ...... create cursor Loop through sysobjects and get all table names in my database. .... exec ('truncate table ' + @TableName)
by: Sumanth | last post by:
Are there any implementations of truncate in db2. Is it going to be implemented in the future? Is there an alternate way of doing a truncate of a table that has a high record count without using "load" and is fast? Thanks, Sumanth
by: Neil | last post by:
Can one use Truncate Table on a linked server table? When I try it, I get a message that only two prefixes are allowed. Here's what I'm using: Truncate Table svrname.dbname.dbo.tablename
by: Sala | last post by:
Hi I want to truncate all data in database ... pls help me how i ll truncate?
by: Sandham | last post by:
I have a series of tables in which the data can be refreshed from a file. Before refreshing the data, some of the tables are cleared using either TRUNCATE or DELETE. If TRUNCATE is used, any index which is not PRIMARY or UNIQUE appears to be disabled and is not updated when new data is inserted into the table. Performing an OPTIMIZE reactivates the index. If DELETE is used instead of TRUNCATE, this problem does not occur.
by: Timothy Madden | last post by:
Hello I see there is now why to truncate a file (in C or C++) and that I have to use platform-specific functions for truncating files. Anyone knows why ? I mean C/C++ evolved over many years now, and still, people making _the_ standards never decided to include such a function. Even in POSIX it was included only in recent versions, mostly not fully supported yet.
by: orajit | last post by:
Hello, I have one concern , I have one table that contains huge data. I have created one procedure inside that procedure I am truncating that table . It takes very long time to execute that. When I saw the Dbms_profiler report I have observed that truncate statement took long time to execute around 20 minutes . Then I replace the truncate with delete . It executed within fraction of time with proper result. May I know why it happened . We...
by: ananthaisin | last post by:
How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not .... I have given truncate statement in a procedure to drop the storage of a table used... when its record count exceeds 500. BEGIN i:= 0; FOR CC IN NLD LOOP
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.