Different between Delete and Truncate and Drop

Truncate:

1.Truncate is Data Definition Language command which is used to remove the all Rows from the table.

2.You can not Filter rows while truncate data from the database because it does not allows where clause.


3.Truncate does not return number of rows truncated from the table.


4.Truncate deallocates the memory for that object and other object will use that deallocated space.
5.Truncate operation can not roll backed because it does not operates on individual row.It directly processes all rows from the table.
6.Truncate is faster than delete.
7.You can not use conditions in case of truncate.
8.truncate======it will do 2 actions
1.drop the table from db
2.after that it will recreate the object with metadata
it releases space occupied by the rows, we can use that space for another purpose by using reuse command. (Suggested By Mohan from Readers )

Syntax:
Truncate table <Tablename>;


Delete:

1.Delete is Data Manipulation Language statement which is used to manipulate the data from the table.

2.Delete Statement does not change any property of database.

3.Delete statement is used to remove the rows from the table.you can use filtering criteria or where condition to remove the specific rows from the table.

4.If You can not specify the where condition all rows will be removed from the table.

5.After using delete you need to commit the changes to make it permanent.

6.It deletes the row by row data from the table so Delete is slower than truncate.

7.Every deleted row is locked,thus it requires more number of locks.

8.This operation uses all Delete triggers.

Syntax:

Delete from tablename


Where column name= condition;


Drop:

1.Drop is Data Definition Language Statement.

2.The Drop command removes the table from the database.

3.It removes all the indexes,privilleges,rows and frees the memory space for other objects.

4.You can not drop the table referenced by foreign key constraint.

5.The objects dependent on the table which we are dropping like Views,procedures needs to be explicitly dropped.

6.No DML triggers will be fired.

7.You can not roll back the drop table operation.

8.drop =====it will delete the db object permanently from db like tables, (Suggested by Mohan by Readers)

Syntax:

Drop table tablename;

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+