Tips and Tricks #1 – SQL Delete Vs. Truncate
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