Tags: , | Categories: Knowledgebase Posted by Ian Blair on 2/25/2011 7:33 PM | Comments (0)

There are two methods to do this either using a TRUNCATE or DELETE command. 
Both have their advantages and disadvantages

TRUNCATE TABLE [TableName]
This method is fast, but should not be used on any table that is used
for replication, or if there are ON DELETE triggers present on the table.
This method will simply remove all the rows in the table in a single operation.

Advantages:
It usually much faster than a DELETE statement.
If you use IDENTITY columns it will reset them.
The wasted space may be reused.

Disadvantages:
You cannot specify a WHERE clause, its all or nothing.
Triggers will not fire.
If the table is replicated you cannot use this method.
If the table has a foreign key it will return an error and fail.
Cannot be rolled back, without a restore.



DELETE FROM [TableName] WHERE xxxxxx
This method works row by row, and fires triggers, and updates the transaction
logs. This method will be slower on large tables than TRUNCATE but should
be the only method you use if you have ON DELETE triggers, or the table is
included in replication.

Advantages:
Triggers are fired for each row deleted.
It works for replicated tables.
You can specify a WHERE clause to remove part/all of the rows of a table.
It can be rolled back without a restore.

Disadvantages:
Speed on large tables.
Recovered space is not immediately reused.
More internal space may be used while deleteing data. An issue if you are removing rows to save space.



Following a DELETE statement the following can be used to reset the table IDENTITY if one is being used.

DBCC CHECKIDENT("[TableName]", RESEED, "reseed_value")

And to recover the wasted space

DBCC SHRINKDATABASE ([DatabaseName]).

Comments are closed