What is the difference among DELETE, DROP and TRUNCATE. Explain

DELETE

DELETE statement is used to delete the rows from the database tables. This will delete entire row from a table, not a single cell or column. DELETE statement deletes the rows from the table that matches the specified condition when used with WHERE clause. DELETE statement can be used with transaction i.e. Deleted rows can be rollback/ undo if used in the transaction block.

Triggers can also be defined and executed on DELETE.

Syntax

BEGIN TRAN
DELETE FROM tableName WHERE condition
--Rollback, if you want to undo
ROLLBACK TRAN
--Commit if you want to save changes
COMMIT TRAN


DROP

DROP statement completely removes the database objects from the database. DROP statement deletes columns from the table, table, database, Index.

Syntax

Drop table

DROP TABLE tableName


Drop Database

DROP Database databaseName


Drop Index

DROP INDEX table_name.index_name

TRUNCATE

TRUNCATE statement deletes all the data from the table, not the table definition itself. Delete rows can not be rollback. Triggers cannot work with TRUNCATE.

Syntax

TRUNCATE TABLE tableName