I’ve answered this question many times, and answered it again this weekend. What is the difference when doing a DELETE TableA instead of TRUNCATE TableA?
A common misconception is that they do the same thing. Not
so. In fact, there are many differences between the two.
DELETE is a logged operation on a per row basis. This means
that the deletion of each row gets logged and physically deleted.
You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.
TRUNCATE is also a logged operation, but in a different way.
TRUNCATE logs the deallocation of the data pages in which the data
exists. The deallocation of data pages means that your data
rows still actually exist in the data pages, but the
extents have been marked as empty for reuse. This is what
makes TRUNCATE a faster operation to perform over DELETE.
You cannot TRUNCATE a table that has any foreign key
constraints. You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.
TRUNCATE will reset any identity columns to the default seed
value. This means if you have a table with an identity column and
you have 264 rows with a seed value of 1, your last record will have
the value 264 (assuming you started with value 1) in its identity
columns. After TRUNCATEing your table, when you insert a new
record into the empty table, the identity column will have a value of
1. DELETE will not do this. In the same scenario, if you
DELETEd your rows, when inserting a new row into the empty table, the
identity column will have a value of 265.
Excellent… very much userful and its exactly what iam looking for
——————————————————————————–
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause
Nice n Informative post
Thnx
Exactly What I was looking for THANKS! and Great post!
Since the data rows still exist after truncate the size of the db doesn’t decrease after running truncate on many tables. Is it possible to release this data and reduce the size of the db?
thank you this is very helpful all over the world =)
Your tutorial is helpful for me.Thanks to YOU
Thanks
how i get back truncated table in sqlserver
Thanks
what is the use of temporary table ?how we use it?
how to pass dynamic parametyers in stored proc?
while inserting data in a table through strored proc and the size of the field is
less than any valued ,how we avoid this situation without changing the size of the field
plz mail to hareshpurohitATtelstra.com
thanks..
Nice explanation; simple and concise.
Very helpful post. Short & to the point. Thanks!!
help ful information
good explain…for better give some example
Wat a gud explation u have…!!!
It’s amazing!!!
really help full for us…
Given insight to the concepts. simply nice….
Hi, thanks for this article , its very easy to understand, i have question, if we use truncate the data will still present in the pages but they marked as empty for reuse,so what about the space,it wont release any space right?
thanks for the post ,can we delete a table that has constraints??
It seems that delete( usually follows a where clause) does not release table size even you delete a whole table.
shows u do know what the shit is going on actually inside…. good one. thanks.
This is very nice explanation ,,,
it is good to understand.
The concepts is good. The other difference is Truncate will be available for the whole table. Delete can be done by row level.
Very nice and cool explanation.
thanks a lot easy to understand.
nice description
Excellent
great job..
Somewhat off topic, but…in a MySQL NDBCluster (in memory table-type), a DELETE won’t free up the memory used by the table – it’ll only make it available for subsequent inserts into it. That is, until the data nodes are re-started.
A truncate will release memory used by a table without the need to restart a data node.
What about the table space concept, will it be freed in both the cases, delete n truncate???
Great Post
Helped me a lot
This Post made me to feel better to answer any one who will ask me the difference
Hi it is really very good artical which is showing the difference between truncate and delete
Thanks for clearing the concept, before geting this answer i thought that in truncate we can not roll back in any how.
thanks again.
Thanks for clearing the concept, before geting this answer i thought that we can not roll back in any how.
thanks again.
Hi,
very simple words, excellent post.
Excellent
plz.
inform me in my id
thanks….
sorry,
i am unable to get my answers
sorry,
i am unable to retive my answers.
what is the use of temporary table ?how we use it?
how to pass dynamic parametyers in stored proc?
while inserting data in a table through strored proc and the size of the field is
less than any valued ,how we avoid this situation without changing the size of the field
plz mail to
thanks..
while inserting data in a table through strored proc and the size of the field is
less than any valued ,how we avoid this situation without changing the size of the field
plz mail to
thanks..
while inserting data in a table through strored proc and the size of the field is
less than any valued ,how we avoid this situation without changing the size of the field
plz mail to
thanks..
Thanks for the info.. very simple words, very concise statements, very lucid explanation
Thanks for this information
which maks me to understand what exactly happens internally.
Thanks…!
Thanks for a nice answer !!
Its possible to use where condition in truncate table command
Q: Is RollBack Possible in both case Delete And Truncate in Sql Server2000 ?
Sol: Yes, in case of delete you can roll back, but in case of truncate, there’s a scenario where rollback is possible. Normally not, you can take truncate as delete + commit as truncate is a DDL Command.
Scenario:
BEGIN TRAN
//DO
TRUNCATE
IF ERROR (OR ANY CONDITION)
ROLLBACK
COMMIT TRAN
in this case, rollback will work even after truncate if it meets your condition.
Other wise, truncate as i mentioned is a DDL Command and will work with auto commit status set as true.
Regards
nice explaination
verry good
simple and nice explaination!!
Thanks,
But what abt RollBack
Is RollBack Possible in both case Delete And Truncate in Sql Server2000
Thanks !! It helped ..
Short but still easy to understand !
Thanks for a great info. It was asked two time in interviw and iwas having no answer
thank for clearing the concept
Gud one!
will help me for my interview
hi I am Rajiv Bhati, i am a S/w Test professional(QA). the biggest problem that i face while performing testing,is this one i.e basic difference between delete table and truncate table. from your material i got some more information.
thanks & regards
Rajiv Bhati rajivbhati12@gmail.com
excellent post !!!
One other difference to note… delete triggers are not fired when using TRUNCATE.
Excellent post, btw
And the other big difference is: permissions.
The fact that a user can DELETE rows does not mean they can TRUNCATE the table. By default, they need at least DDL_ADMIN to do that.
nice explanation, I was asked this in an interview and couldn’t really explain the difference. thanx….
Add this to the pile of stuff I didn’t know before I met you. Thanks!
Simple concept + profound implications = good post.