Difference between TRUNCATE , DELETE and DROP in SQL Server

The difference between TRUNCATE , DELETE and DROP is one of the most common interview question. 
 
TRUNCATE
  • TRUNCATE is a DDL command
  • TRUNCATE is executed using a table lock and whole table is locked for remove all records.
  • We cannot use Where clause with TRUNCATE.
  • TRUNCATE removes all rows from a table.
  • Minimal logging in transaction log, so it is performance wise faster.
  • TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
  • Identify column is reset to its seed value if table contains any identity column.
  • To use Truncate on a table you need at least ALTER permission on the table.
  • Truncate uses the less transaction space than Delete statement.
  • Truncate cannot be used with indexed views.
DELETE
  • DELETE is a DML command.
  • DELETE is executed using a row lock, each row in the table is locked for deletion.
  • We can use where clause with DELETE to filter & delete specific records.
  • The DELETE command is used to remove rows from a table based on WHERE condition.
  • It maintain the log, so it slower than TRUNCATE.
  • The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
  • Identity of column keep DELETE retain the identity.
  • To use Delete you need DELETE permission on the table.
  • Delete uses the more transaction space than Truncate statement.
  • Delete can be used with indexed views.
DROP
  • The DROP command removes a table from the database.
  • All the tables' rows, indexes and privileges will also be removed.
  • No DML triggers will be fired.
  • The operation cannot be rolled back.
  • DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
  • DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Please give your valuable suggestions and feedback.

Ebook Download
View all
Learn
View all