Difference between Delete and Truncate in SQL server

Introduction:

Difference between DELETE and TRUNCATE in SQL Server is one of the most important interview questions asked to fresher. I have tried to explain it here so that fresher can better understand the difference between these two.

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to delete specified records based on conditions. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. It removes rows from a table or view. DELETE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information.

e.g.

•  delete from employee ;( this command will remove all the data from employee table) 
• delete from employee where emp_id=100;(This command will remove only that row from employee table where emp_id=100);

TRUNCATE

TRUNCATE removes all rows from a table without logging the individual row deletions .No triggers will be fired in TRUNCATE. As such, TRUNCATE is faster and doesn’t use as much undo space as a DELETE.

e.g. truncate table employee.( This command will remove all the data from the employee  table)

DELETE vs TRUNCATE

1)     Counter of the Identity column is reset in Truncate where it is not reset in Delete.

2)     Delete keeps the lock over each row where Truncate keeps the lock on table not on all the row.

3)  Speed:    TRUNCATE is much faster than DELETE. The reason is when you type DELETE all the data get copied into the Rollback Tablespace first and then delete operation get performed. That is why in case of  ROLLBACK , after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process takes time. But in case of TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That’s why TRUNCATE is faster. Once you truncate you can’t get back the data.(but in fact it can be rolled back as i proved in the end of the section.)

4)     Truncate is faster in performance wise, because it is minimally logged in transaction log. Delete is slower than truncate because, it maintain logs for every record

5)     DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table. TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

6)     In truncate we cannot use WHERE Clause where as in delete we can specify 

filters in WHERE clause.

7)     TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. Delete activates a trigger because the operation is logged individually.

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

9) If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column. DELETE retain the identity. This means if you have a table with an identity column and you have 100 rows with a seed value of 1, your last record will have the value 100 (assuming you started with value 1) in its identity columns.  After truncating your table, when you insert a new record into the empty table, the identity column will have a value of 1 but DELETE will not do this.  In the same scenario, after deleting rows, when inserting a new row into the empty table, the identity column will have a value of101.

10) As TRUNCATE is a DDL (data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other hand DELETE is a DML (data manipulation language) statement hence requires explicit commit to make its effect permanent.

11) We may use DELETE statement against a view (with some limitations). But we can’t use TRUNCATE statement against a view.

Rollback in DELETE and TRUNCATE

Myth: We cannot rollback in TRUNCATE but in DELETE we can rollback.

But this is not true. Truncate can also be rolled back if used with transaction. Lets proove it:

DELETE example:

BEGIN TRAN

DELETE EMPLOYEE

SELECT * FROM EMPLOYEE (no data because of DELETE command)

ROLLBACK

SELECT * FROM EMPLOYEE (data is rolled back because of ROLLBACK command)

TRUNCATE example:

BEGIN TRAN

TRUNCATE TABLE EMPLOYEE

SELECT * FROM EMPLOYEE (no data because of TRUNCATE command)

ROLLBACK

SELECT * FROM EMPLOYEE (data is rolled back because of ROLLBACK command similar to DELETE command)