Difference between Delete, Truncate and DROP

Here is the difference between Delete, Truncate and DROP in Relational Database Management System.

Delete

Delete Operation is performed to remove/delete specific data from Table.

Syntax:

DELETE from TABLE WHERE condition;

Example:

DELETE from tbl_users WHERE user_id = 1;

1. We can Rollback after Delete.

2. Delete does not Reset Identity of Table.

3. It locks the Table Row.

4. It is a DML Command.

5. WHERE can be used with the Query.

6. Trigger is Fired.

Truncate

Truncate Operation is performed to remove/delete all data from Table and Re-Create the Table with same structure.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE tbl_users;

1. We can't Rollback after Truncate.

2. Truncate Resets Identity of Table.

3. It locks the Entire Table.

4. It's DDL Command.

5. WHERE can't be used.

6. Trigger is not Fired.

How to Perform Truncate and Delete in MS SQL Server Database?

DROP

DROP Operation is performed to delete Table from the Database.

Syntax:

DROP TABLE table_name;

Example;

DROP TABLE tbl_users;

1. The DROP command removes a table from the database.

2. All the tables' rows, indexes, and privileges will also be removed.

3. No DML triggers will be fired.

4. The operation cannot be rolled back.

How to Alter or Drop Table in MS SQL Server Database?