Truncating Large Tables

Question

I have a table that contains 700 million data, what is the best way to empty this table in a fastest way.I believe using delete statements is not suitable for such operations because it will log the whole transactions.

Answer 1

As your title says - if you are looking for the fastest way possible then you should truncate the table.

TRUNCATE TABLE [dbo].[mytable]

Truncating the table has the following advantages:

Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

• Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

• Fewer locks are typically used.

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

• Without exception, zero pages are left in the table.

After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Truncating will also reset the identity though so if you need to keep this you should either get the identifier first (script out the table) and then set the table back to that after the truncate. Alternatively you can just script out the table, drop it and re-create it.

Alternatively you can easily SWITCH out the table data with the following

--May need to script out table if you have things like compression.
select TOP 0 * into mytable_old from mytable; 

ALTER TABLE mytable SWITCH TO mytable_old;

DROP TABLE mytable_old;

Switching data is a meta data change, no data is actually moved on the disk so this typically is a fairly quick (assuming no blocking on the table) operation and VERY little I/O. Dropping the table will of course incur I/O penalties as normal.

Lastly - if for some reason you have A BUNCH of locks on the table, you may want to slowly delete records from the table. This is not quick, however when I have used it, it keeps blocking to a minimum as the delete windows are quick. This will write a lot more information to the log though so if you have something like replication, this could make the log reader agent go nuts as it will have way too many records to process (there are entire topic on workarounds for this but I'm getting off track). That all being said - this is more of a special case delete and probably should only be used as a last resort (or when you need to specify certain things to delete).

;WITH CTE AS
(
   select TOP 1000 *  -- Depending on the blocking, you may want to up/down this amount
   from mytable
)
delete from CTE;

WHILE (@@ROWCOUNT > 0)
BEGIN
   WAITFOR DELAY '00:00:03'; -- Gives times for other queries to process/helps with blocking

   ;WITH CTE AS
   (
     select TOP 1000 *
     from mytable
   )
   delete from CTE;
END

Answer 2

Three ways as far as I can see

1. Truncate table
2. Script out the table, then drop table and recreate the table using the script.
3. Assuming the table has a PK, then create a similar table, and using partition switch in/out to switch the table to the new table, and then drop the newly created table.

Welcome more ideas for this simple yet fun question.

Answer 3

If you are deleting all the rows from that table then simplest option would be to Truncate table as follows:

TRUNCATE TABLE TableName

Ref: How to delete large data of table in SQL without log?

Answer 4

The bes way is TRUNCATE TABLE

TRUNCATE TABLE [Schema].[TableName]