19

I have this table in a SQL Server 2008 R2 instance which I have a scheduled process that runs nightly against it. The table can have upward to 500K records in it at any one time. After processing this table I need to remove all rows from it so I am wondering which of the following methods would produce the least overhead (ie Excessive Transaction Log entries):

  1. Truncate Table
  2. Drop and recreate the table

Deleting the contents of the table is out due to time and extra Transaction log entries it makes.

The consensus seems to be Truncation, Thanks everyone!

Mark Kram
  • 5,672
  • 7
  • 51
  • 70
  • 4
    [Some comparisons between amount of logging here](http://dba.stackexchange.com/a/7685/3690). `TRUNCATE` has slightly less logging than `DROP` as dropping also causes the associated rows for that object to be deleted from the system tables (which is also logged) – Martin Smith Feb 01 '12 at 20:47

4 Answers4

17

TRUNCATE TABLE is your best bet. From MSDN:

Removes all rows from a table without logging the individual row deletes.

So that means it won't bloat your transaction log. Dropping and creating the table not only requires more complex SQL, but also additional permissions. Any settings attached to the table (triggers, GRANT or DENY, etc.) will also have to be re-built.

Yuck
  • 49,664
  • 13
  • 105
  • 135
11

Truncating the table does not leave row-by-row entries in the transaction log - so neither solution will clutter up your logs too much. If it were me, I'd truncate over having to drop and create each time.

acoffman
  • 708
  • 3
  • 10
6

I would go for TRUNCATE TABLE. You can potentially have overheads when indexes, triggers, etc get dropped. Plus you will lose permissions which will also have to be re-created along with any other required objects required for that table.

Also on DROP TABLE in MDSN below it mentions a little gotcha if you execute DROP and CREATE TABLE in the same batch

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

kevchadders
  • 8,335
  • 4
  • 42
  • 61
3

Dropping the table will destroy any associated objects (indexes, triggers) and may make procedures or views invalid. I would go with truncate, since it won't blow up your log and causes none of the possible issues a drop and create does.

Mithrandir
  • 24,869
  • 6
  • 50
  • 66
  • Thanks, there aren't any associated indexes or triggers, I am just worried about the transaction log growing too big. – Mark Kram Feb 01 '12 at 20:54
  • I learned this the hard way when I started adding extended properties to my tables, and because my t-sql scripts used drop-and-create, I had to re-enter the extended properties and change the scripts to truncate. No lost extended properties after that. –  Apr 29 '16 at 21:58