Truncate or Drop and Create Table

sql-server tsql truncate
Mark Kram picture Mark Kram · Feb 1, 2012 · Viewed 11.7k times · Source

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!

Answer

Yuck picture Yuck · Feb 1, 2012

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.

Related questions

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a DELETE without a …

Read More
Pros & Cons of TRUNCATE vs DELETE FROM

Could someone give me a quick overview of the pros and cons of using the following two statements: TRUNCATE TABLE dbo.MyTable vs DELETE FROM dbo.MyTable It seems like they both do the same thing when all is said …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More