Skip to content

Tips and Tricks #1 – SQL Delete Vs. Truncate

January 3, 2010

I just learned something new about SQL today…you can DELETE a table or TRUNCATE a table.  I have always heard that you NEVER want to TRUNCATE a log, so the word truncate always makes me uneasy. 
However, I recently had a database table (a table that only stored error information for an application we use) that grew wildly out of control (4,000,000+ records).  After fixing the application that generated all this error information’s problem, all I wanted to do was get rid of the 4,000,000+, errr..50Gb of data, rows.  The DELETE statement was taking forever to run.  I was having to delete about 100,000 rows at a time and the execution took 2 hours (if you pull out your calculator, that would have been 20 hours to delete the rows of the table).  After talking with one of our partners, they said I should use the TRUNCATE statement.  Truncating the table took about 30 seconds.  I then ran a SHRINK command that night and whola…I had my 50GB’s back the next morning!
Obviously this is not an ideal solution for most situations…only situations where deleting the all the rows in the table is ok.
Truncate table xxx erase ALL table data quickly
Delete * from xxx deletes row at a time given the specified criteria takes a while and logs the transactions

From → SQL

2 Comments
  1. Hi Ron,

    Thought I would share what I know in this regard.

    One more critical difference between DELETE & TRUNCATE:

    DELETE can be rolled back, if we don’t want to commit that deletion operation (provided AUTO COMMIT property is set to false).

    TRUNCATE is permanent. We cannot roll back the records.

    But, time factor difference between these two is new that I learned from your article. Thanks so much for that.

    Thanks
    Vaidy

  2. Ron permalink

    Thanks for that Vaidy. I remember when this issue came up, the LAST thing I wanted to do was TRUNCATE. Due to the sheer number of rows, however, I didn’t have enough hard drive space to execute a DELETE operation. After much research, I found that the TRUNCATE would do what I needed and it did it FAST!!!

    I really don’t think I realized that you could rollback a DELETE command if auto commit was set to false. Thanks for the insight.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: