Both of TRUNCATE and DELETE are the statement which we use to remove data. But many people are considering the differences between TRUNCATE and DELETE and which one is better and faster?

1-TRUNCATE removes all rows without logging the individual row deletions instead SQL Server marks and de-allocates the data pages used to store the table data and then records only the page de-allocations in the transaction log. WHERE  statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, DELETE does not free the space containing the table while TRUNCATE does free the space containing the table.

2- In DELETE, we can specify the WHERE clause but TRUNCATE can not be.

3-In case, we compare DELETE (without WHERE) and TRUNCATE, TRUNCATE is faster because it is minimally logged in the transaction log. Otherwise, DELETE maintains logs for every record.

4- TRUNCATE requires TABLE lock or PAGE lock while DELETE requires ROW lock.

5-TRUNCATE TABLE can not active a trigger because the operation does not log individual row deletions but DELETE actives a trigger because the operation is logged individual.

6-TRUNCATE resets IDENTITY value of the table but DELETE does not.

7-We can use DELETE on a parent table if CASCADE DELETE is enabled then data from child tables also get deleted. If CASCADE DELETE is not enabled and any of child table has related, you can not delete records from the parent table. Otherwise, we can not truncate a parent table.

8-We can use DELETE on the table even if we have Replication/CDC enabled for the table. Otherwise, TRUNCATE can not be.

9-DELETE permissions default to members of sysadmin fixed server role, the db_owner and db_datawriter fix database role, and the table owner. Minimum permission required is DELETE permissions on the target table. SELECT permissions are also required if the statement contains a WHERE clause. We db_ownerddl_admin, or owner of the table to be able to fire a TRUNCATE statement and minimum permission required is ALTER table.

10-We may use DELETE against a view (some limitations) but TRUNCATE can not be used.

11- DELETE is DML language and TRUNCATE is DDL language.

12- Records removed using DELETE can be roll backed and can be restored point in time. Records removed using TRUNCATE can ALSO be roll backed if we use  it in a transaction.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s