The Difference between Truncate and Delete in SQL Server

  • Nice introduction to delete and truncate statements. Glad you mentioned the difference in how identity values are not reseeded with a delete command as that is definitely an important aspect to consider. That bit me once when I was starting out and I had to go back and fix the identity values manually. 🙁

  • pnewhart (7/7/2011)

    virat_108 (4/14/2011)

    You will get better and easy explanation over here:

    That blog post states that delete cannot be rolled back. This is inaccurate. Both truncate and delete can be rolled back if they are within a begin tran/commit/rollback statement.

    That blog post also states that DELETE doesn't "activate triggers".

    Careful with what you read because there's a lot of misinformation on the internet. 🙂

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst

  • Hey Prashnat,

    This is a beatiful article which you have poasted and very useful.

  • JustStarted (7/6/2011)

    Good article. I knew that Truncate is minimal logged operation but didn't knew that it logs deallocation of data pages.

    I have one question though. I know Delete is row by row operations. What happens if we delete rows in bulk? How SQL Sever logs this operation? If we do bulk delete, is it possible to rollback if it is not wrapped in transaction?


    What do you mean by bulk delete? there is no 'bulk delete' such a command in sql.

    Delete will write every rows in log file.

    If you didn't start within the transaction then you can't rollback because SQL server by default auto commit the transaction.

    Muthukkumaran Kaliyamoorthy

  • Muthukkumaran Kaliyamoorthy, please refer to this post for your answer. I happen to post it in different window instead of replying to your post 😀

    True there is no such command called "Bulk Delete" in SQL. But you can wrapped DELETE statement in transaction to delete records from your table. Deleting is bulk is faster than just issuing DELETE command. Here's the code snippet. It deletes records in batch of 100000 rows at a time.



    DECLARE @totalRowint;

    DECLARE @intRowCount int;

    DECLARE @intErrNoint;

    SELECT @totalRow = COUNT(*) FROM [Table] WITH (NOLOCK)

    WHERE [Condition] OPTION(MAXDOP 2);

    SELECT@intRowCount = @totalRow,

    @intErrNo = 0;

    WHILE @intRowCount > 0 AND @intErrNo = 0


    SET ROWCOUNT 100000;

    DELETE FROM [Table]

    WHERE [Condition] OPTION(MAXDOP 2);

    SELECT @intErrNo = @@ERROR

    , @intRowCount = @@ROWCOUNT;


    SET ROWCOUNT 0; -- Reset batch size to "all"


  • HI this was nice post

    for further more simple practical example visit

  • Hi for more practical explanation and conceptual funda visit

Viewing 7 posts - 61 through 66 (of 66 total)

You must be logged in to reply to this topic. Login to reply