Re: Difference Between Truncate and Delete

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 08/01/04 

Date: Sat, 31 Jul 2004 22:54:40 -0700

Actually, TRUNCATE is logged, but what is logged is not the individual rows,
but the page and extent deallocations. It is more than simply moving a file
pointer, as tables are NOT files.
TRUNCATE can be inside a transaction, and if the transaction is rolled back,
the deallocated pages are remarked as allocated.

It is not true that TRUNCATE is practically instantaneous, although it can
seem that way compared to DELETE of individual rows.One of my clients had a
table so big that it took over 20 minutes to do a TRUNCATE, but we never
timed a DELETE. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"me" <jscott56@teranews.com> wrote in message
news:HN_Oc.78007$vJ6.76709@cyclops.nntpserver.com...
> delete causes the rows to be deleted, as part of a transaction, and is
> logged to the log file.
>
> truncate resets the end of file pointer, effectively removing reference to
> all the rows.  No transaction log entry, and no opportunity to recover the
> data from the log.
>
> truncate will run much much faster on tables with many rows than will the
> delete.  Its nearly instantanious.
>
>
> "Panks" <agarwalp@eeism.com> wrote in message
> news:OotXT97aEHA.1840@TK2MSFTNGP11.phx.gbl...
> > What is the difference between Truncate and delete Statements
> >
> > Eg
> >
> > DELETE from table1  -- deletes all the rows , so does TRUNCATE table
> table1
> >
> >
>
>

Relevant Pages

  • Re: Simple Question on DTS - Please help me ..
    ... One way to do this is to use a transaction. ... The second is the data transform to that table. ... Create two ExecuteSQL tasks on Connection B with the statement "rollback ... I can achive the truncate statement by Execute sql task but i want to ...
    (microsoft.public.sqlserver.dts)
  • Re: Log Invalidated after truncate table
    ... >transaction log for the entire database. ... >until the next full database backup. ... Restore a full backup to restore the database to the state it had ... A TRUNCATE TABLE operation is minimally logged. ...
    (comp.databases.ms-sqlserver)
  • Re: Difference Between Truncate and Delete
    ... > but the page and extent deallocations. ... > TRUNCATE can be inside a transaction, and if the transaction is rolled back, ...
    (microsoft.public.sqlserver.programming)
  • Re: [Ext2-devel] [RFC] [PATCH] Reducing average ext2 fsck time through fs-wide dirty bit]
    ... What you suggest IS essentially the fallback. ... behaviour is only acceptable because it ensures that the whole truncate ... can be completed in a single transaction. ... worst case truncate dirties every group in the filesystem. ...
    (Linux-Kernel)
  • Re: urgent!!!
    ... One of us has an incomplete and/or inaccurate understanding of SQL Server in this context. ... > a BEGIN TRANSACTION & END TRANSACTION statement. ... > Deleting All Rows Using TRUNCATE TABLE ...
    (microsoft.public.sqlserver.server)