Tuesday, May 22, 2012

Difference between Truncate and Delete statement, SQL

In SQL server 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.

Before moving to the details lets first understand the basic SQL statement types:

  1. DML - Data Manipulation Language: It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements.
  2. DDL - Data Definition Language: It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements.
  3. DCL - Data Control Language: It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements.
  4. TCL - Transactional Control Language: It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK statments.

Difference - TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is release back to the server. However, DELETE is a DML command and can be rolled back.

When we use this command, SQL server deletes one row at a time. Each row is logged in the transaction LOG. This also means that the server will also maintain the Log Sequence number. This will consume more database resources 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 table 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.

No comments:

Post a Comment