Delete vs Truncate

Its a common interview question which people like to ask to know your understanding on RDMS concepts. I too few times encountered the question in interviews. Pleases don't ask me interview result. Haha ha...

DELETE:

1. DELETE is a DML Command. 

2. DELETE statement is executed using a row lock, each row in the table is locked for deletion. 

3. We can specify filters in where clause 

4. It deletes specified data if where condition exists. 

5. Delete activates a trigger because the operation are logged individually. 

6. Slower than truncate because, it keeps log and every deleted row must x-locked before delete. 

7. Need more locks and CPU also if number of rows are more because managing/maintaining locks is CPU intensive operation.

7. Rollback is possible.

TRUNCATE:

1. TRUNCATE is a DDL command.

2. TRUNCATE TABLE always locks the table and page but not each row.

3. Cannot use Where Condition.

4. Removes all the data.

5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

6. Faster in performance wise, because it doesn't keep any logs.

7. Rollback is not possible.




DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we cannot rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.