Re: Difference Between Truncate and Delete

From: me (jscott56_at_teranews.com)
Date: 08/01/04 

Date: Sun, 1 Aug 2004 01:03:09 -0400

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: truncate log file for replication database
    ... Looking for a SQL Server replication book? ... I encountered problem when I tried the truncate the log file the 2nd time ... We just start the transaction replication for the database few month ...
    (microsoft.public.sqlserver.replication)
  • Re: another logging question
    ... I tested this and found out that I can't set the log file to 1M and then not ... A large transaction fills up the log. ... However, under the simple recovery plan, the log file does truncate at the ...
    (microsoft.public.sqlserver.server)
  • Re: truncate log file for replication database
    ... I tried to stop the log reader from the distributor, ... Now I am unable to truncate the log file. ... We just start the transaction replication for the database few month back. ...
    (microsoft.public.sqlserver.replication)
  • Re: truncate log file for replication database
    ... We just start the transaction replication for the database few month back. ... Due to the heavy transaction on the database, we schedule daily log file ... Ensure the Log Reader Agent is running or use ... there must some standard way to truncate the log file for a live ...
    (microsoft.public.sqlserver.replication)
  • Re: Log Invalidated after truncate table
    ... > just enough information in the log file to rollback if the transaction ... TRUNCATE TABLE logs the extent deallocations, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)