none
Difference between truncate and delete

Answers

  •  Suprotim Agarwal wrote:

    Hi,

     

    1. Truncate cannot be rollbacked(deallocation of space occurs). Delete can be.

    2. Truncate is a DDL command. Delete is a DML command.

    3. Truncate statements cannot have WHERE. Delete can have.

    4. Truncate is faster than Delete as there are no rollback/commit operations associated with it.

    5 .Truncate does not check constraints. Delete checks before deleting rows

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

     

     

    You can certainly rollback with truncate table.

     

    e.g.

    Code Snippet

    select *
    into trn
    from sysobjects
    go
    select 'count before truncate: '+cast(count(*) as varchar) from trn
    go
    begin tran
    truncate table trn
    select 'count after truncate: '+cast(count(*) as varchar) from trn
    rollback
    select 'count after rollback: '+cast(count(*) as varchar) from trn
    go
    drop table trn
    go

     

     

    Friday, September 14, 2007 8:45 AM
  • Code Snippet

    USE YourDB

    GO

    -- create a couple of tables

    CREATE TABLE BigTable1 (int1 int, int2 int, char1 char(1), char2 char(1))

    CREATE TABLE BigTable2 (int1 int, int2 int, char1 char(1), char2 char(1))

    -- populate a bigish table with our control data

    DECLARE @Date DATETIME

    SET @Date = DATEADD(ss, 90, GETDATE())

    WHILE GETDATE() < @Date

    BEGIN

    INSERT INTO BigTable1

    SELECT 1,2,'B','C'

    END

    -- STARTTEST

    -- populate the bigtable2 from bigtable1

    INSERT INTO BigTable2 SELECT * FROM BigTable1

    -- backup the database to truncate the log

    BACKUP DATABASE YourDB TO DISK = 'c:\YourDB.bak'

    -- check log space used before (note the logspaceused is a percentage of Logsize MB)

    DBCC SQLPERF(LOGSPACE)

    -- 1st run delete data

    DELETE BigTable2

    -- 2nd run truncate table

    --TRUNCATE TABLE BigTable2

    -- check log space used after

    DBCC SQLPERF(LOGSPACE)

    -- now go back to STARTTEST for the 2nd run

    -- tidy up

    DROP TABLE BigTable1

    DROP TABLE BigTable2

     

     

    HTH!

    Friday, September 14, 2007 9:26 AM

All replies

  • Functionally they are do the same thing (although you can't add a WHERE condition to a TRUNCATE statement). However, the main difference is how the data is removed and logged in the transaction log.

     

    A DELETE statement willl log the deletion of each row in the transaction log wherease TRUNCATE will just log the deallocation of the pages the data is on.

     

    TRUNCATE uses less resources so you will often see a massive speed difference in removing rows, particuarly with large tables.

     

    Check Books Online: Deleting All Rows by Using TRUNCATE TABLE for more detail:

     

    HTH!

     

    • Proposed as answer by LearnerSql Monday, September 05, 2011 5:49 PM
    Friday, September 14, 2007 7:52 AM
  • Hi,

     

    1. Truncate cannot be rollbacked(deallocation of space occurs). Delete can be.

    2. Truncate is a DDL command. Delete is a DML command.

    3. Truncate statements cannot have WHERE. Delete can have.

    4. Truncate is faster than Delete as there are no rollback/commit operations associated with it.

    5 .Truncate does not check constraints. Delete checks before deleting rows

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

     

    Friday, September 14, 2007 7:55 AM
  • Thanks..any practical example to visually check how the logs differ for TRUNCATE and DELETE?

     

     

    Friday, September 14, 2007 8:40 AM
  •  Suprotim Agarwal wrote:

    Hi,

     

    1. Truncate cannot be rollbacked(deallocation of space occurs). Delete can be.

    2. Truncate is a DDL command. Delete is a DML command.

    3. Truncate statements cannot have WHERE. Delete can have.

    4. Truncate is faster than Delete as there are no rollback/commit operations associated with it.

    5 .Truncate does not check constraints. Delete checks before deleting rows

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

     

     

    You can certainly rollback with truncate table.

     

    e.g.

    Code Snippet

    select *
    into trn
    from sysobjects
    go
    select 'count before truncate: '+cast(count(*) as varchar) from trn
    go
    begin tran
    truncate table trn
    select 'count after truncate: '+cast(count(*) as varchar) from trn
    rollback
    select 'count after rollback: '+cast(count(*) as varchar) from trn
    go
    drop table trn
    go

     

     

    Friday, September 14, 2007 8:45 AM
  • A good writeup on truncate is here.

    http://msdn2.microsoft.com/en-us/library/ms177570.aspx

     

     

     

    Friday, September 14, 2007 8:48 AM
  • Dear oj,

     

    Yes indeed. Thanks for the correction.

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

    Friday, September 14, 2007 8:51 AM
  • You can visually check the logs using a 3rd party product such as LogExplorer.

     

    However, i'd suggest to get an idea of how they differ all you really need do is check how much more the grows using a delete as opposed to a truncate.


    Do the test in an isolated database and test both scenarios, checking the log space used (DBCC SQLPERF(LogSpace)) before and after each one.

     

    HTH!

     

     

    Friday, September 14, 2007 8:58 AM
  • thanks..can i use the your example and replace DELETE instead of TRUNCATE to see the effect?..because i need both DELETE and TRUNCATE for the same example to see the effect.. 

     

    Friday, September 14, 2007 9:01 AM
  • I tried the example which u posted but when i replaced truncate with delete it does show same result as for DELETE..pls help

     

    Friday, September 14, 2007 9:22 AM
  • Code Snippet

    USE YourDB

    GO

    -- create a couple of tables

    CREATE TABLE BigTable1 (int1 int, int2 int, char1 char(1), char2 char(1))

    CREATE TABLE BigTable2 (int1 int, int2 int, char1 char(1), char2 char(1))

    -- populate a bigish table with our control data

    DECLARE @Date DATETIME

    SET @Date = DATEADD(ss, 90, GETDATE())

    WHILE GETDATE() < @Date

    BEGIN

    INSERT INTO BigTable1

    SELECT 1,2,'B','C'

    END

    -- STARTTEST

    -- populate the bigtable2 from bigtable1

    INSERT INTO BigTable2 SELECT * FROM BigTable1

    -- backup the database to truncate the log

    BACKUP DATABASE YourDB TO DISK = 'c:\YourDB.bak'

    -- check log space used before (note the logspaceused is a percentage of Logsize MB)

    DBCC SQLPERF(LOGSPACE)

    -- 1st run delete data

    DELETE BigTable2

    -- 2nd run truncate table

    --TRUNCATE TABLE BigTable2

    -- check log space used after

    DBCC SQLPERF(LOGSPACE)

    -- now go back to STARTTEST for the 2nd run

    -- tidy up

    DROP TABLE BigTable1

    DROP TABLE BigTable2

     

     

    HTH!

    Friday, September 14, 2007 9:26 AM
  • The one significant functional difference is that DELETE (without a WHERE clause) will not reset IDENTITY columns to their initial seed values, whereas TRUNCATE will. Beyond that difference, consider TRUNCATE to be just a more efficient version of DELETE for cases where you need to empty a table.
    Friday, September 14, 2007 12:18 PM
  • Hi senthilkr,

    If you are looking for "Difference between truncate and Delete" You can take a look of the following article.
    http://www.sqlservercentral.com/articles/delete/61387/


    Wednesday, November 28, 2007 5:40 AM