DELETE FROM and TRUNCATE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DELETE FROM and TRUNCATE

What is the difference between Q1) and Q2) as follows? DELETE FROM TABLE1 TRUNCATE TABLE TABLE1 I have observed that in some cases first query works while second one fails.
Ashish Johri
If there are foreign key relationships where table 1 is the "parent" table, then TRUNCATE will fail. Note that in that case, when you use DELETE FROM, the corresponding rows on the child table(s) will also be deleted.
Also DELETE is a fully-logged operation, whereas truncate is minimally logged – i.e. only page deallocations are logged, not physical deletes. Thus TRUNCATE is normally much quicker than delete and produces much less transaction log.
I was looking for the answer from Adriaan. Thanks thomas for fruitful knowledge.
Just to add one more. When we truncate then space is freed while in deleting the space is not freed. Ashish Johri
For deleted data, I believe you need to reindex the table to get the free space…
For trucate sql server deallocate the pages/extents, for reuse…
MohammedU.
Moderator
SQL-Server-Performance.com
Yes the space will be freed and in order to recliam or check on enterprise manager or query analyzer run DBCC UPDATEUSAGE on that table. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Another difference between the two – truncate will always wipe *all* data from the table, while a delete may be limited by a WHERE clause, thus leaving some data untouched. ..oh, and one other thing also.
TRUNCATE – resets IDENTITY
DELETE – does not reset IDENTITY
(should the table have an identity column) /Kenneth

http://www.sql-server-performance.com/q&a118.asp Madhivanan Failing to plan is Planning to fail
quote:Originally posted by Adriaan
Note that in that case, when you use DELETE FROM, the corresponding rows on the child table(s) will also be deleted.

Only when "ON DELETE CASCADE" is in effect. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

And here is another You cannot TRUNCATE a table variable. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

quote:Originally posted by Roji. P. Thomas
quote:Originally posted by Adriaan
Note that in that case, when you use DELETE FROM, the corresponding rows on the child table(s) will also be deleted.

Only when "ON DELETE CASCADE" is in effect. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
… and if not, then the delete action prompts an error message!
]]>