Welcome to ViSh’s Blogs

In SQL server 2000 there are 2 main keywords for deleting data – Truncate and delete. Although the end result might be same but both work very differently. We should take into consideration the advantages, limitation and the consequences when using one of them.

When we use the delete statement, SQL server deletes one row at a time. Each row is logged in the transaction LOG. This also means theta the server will also maintain the Log Sequence number. This will consume more database more resource in the database and the process will be slow. But this also gives an advantage. The transaction can be rolled back as there are transaction log.

Also you can use the where clause with the delete command but not with the truncate command. With truncate command it’s all records or nothing. Also one more advantage of the truncate command is that it also resets the identity seed of the table. Also the fact that deallocated pages is returned to the system for use in other areas.

Truncate statements cannot be used on the tables involved in log shipping or replication. This is because they both depend on the transaction log to keep the database consistent. Truncate table cannot be used with tables having foreign key references. As the truncate command do not fire any triggers. If you want to use the truncate command with a foreign key, you need to first drop the index and then add it again after using the truncate table command.