Delete, Truncate and Drop Command in SQL Server

In this blog am going to talk about some confusing and most frequent asked commands in SQL, these are near
most similar but functionality is different in each case. Here we go:

DELETE Command

Delete Command is used for deleting or eliminating a particular entry or a row from the table using
'WHERE' clause. If you will give a condition through a WHERE clause then it will remove few rows from
your table otherwise it will remove all the rows.
After applying DELETE clause on your table, you can also apply COMMIT or ROLLBACK operation in order
to undo your changes in the particular table.

Example

DELETE from EMP_TABLE
where (AGE=21);

Keys

  • Commit and rollback are possible
  • Requires more space
  • Triggers can be fired
  • It is a DML command

TRUNCATE Command

Truncate directly removes all the rows available in the table. It does not require any WHERE clause in its statement while applying changes on the table. No COMMIT or ROLLBACK operation will possible after applying TRUNCATE command.

This command is faster than the delete command as, it does not take much memory space while performing certain operation.

Example

TRUNCATE EMP_TABLE;

Keys

  • Commit and rollback are not possible
  • Requires very less space
  • No need to fire Triggers
  • Faster execution
  • It is a DDL command

DROP Command

Drop command is used for eliminating the whole table from your database. No COMMIT or ROLLBACK
operation will possible after applying TRUNCATE command.

Example

DROP EMP_TABLE:

Keys

  • Commit and rollback are not possible
  • Requires no space
  • No need to fire Triggers
  • More fatser 
Ebook Download
View all
Learn
View all