Difference between SQL Server Delete,Truncate and Drop Commands

What are the Key differences between Delete,Truncate and Drop Commands
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. 

Notes :

The Truncate will Fail when there is Foreign key violation, and will get the below error.

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

To avoid this error, we can set the below properties.

SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE Table TableName; 

--Set back the Check again to default.

SET FOREIGN_KEY_CHECKS = 1;

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.

 /* Deletes all records from a Table */

DELETE From TableName ;

/* Delete records based on Condition*/

DELETE From TableName Where FieldName='Value' 

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.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back (undone) easily, while DROP and TRUNCATE 
operations also can be rolled back if we define them in a TRANSACTION.

DROP Table TableName; 

Note :
We can rollback DELETE, TRUNCATE and DROP Operations when we defined them in a TRANSACTION with Save Points.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------