What is the difference among DELETE, DROP and TRUNCATE. Explain
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.
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 statement completely removes the database objects from the database. DROP statement deletes columns from the table, table, database, Index.
DROP TABLE tableName
DROP Database databaseName
DROP INDEX table_name.index_name
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.
TRUNCATE TABLE tableName