Difference between DROP, DELETE AND Truncate?

Answer 1

i)DELETE:

1. Removes Some or All rows from a table.
2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.
4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.
5. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.

Answer 2

iii)DROP:
1. The DROP TABLE command removes one or more table(s) from the database.
2. All related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.
3. Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
4. Cannot drop a table that is referenced by any Foreign Key constraint.

Answer 3

ii)TRUNCATE:
1. Removes All rows from a table.
2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. With SQL Server 2016 you can Truncate a Table Partition, for more details check.
4. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.

Answer 4

Good definitions here I just going to provide a link that not only has definitions about what is different between DELETE, TRUNC, and DROP commands also provide basic examples how to use them.

http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Answer 5

Truncate:-
1. It is a DDL command.
2. It is faster than delete.
3. The transaction cannot be rolled back in truncate.
4. We cannot put up any conditions in truncate.
5. In truncate, all rows are deleted.
6. Truncate operations drop and recreate the table, which is much faster than deleting rows one by one.
7. Truncate operations are not transaction-safe.
8. In truncate, the number of deleted rows are not returned.

Delete:-
1.Delete is a DML command.
2. Delete is slower than truncate.
3. Transaction can be rolled back in delete.
4. In delete, we cab write conditions using 'where' clause.
5. In delete, some or all rows are deleted but the structure of the table remains the same.
6. If a delete statement without a 'where' clause is issued then, all rows are deleted.
7. In delete, the number of deleted rows are returned.

Drop:-
1. Drop is a DDL command.
2. It removes one or more tables from the database along with its structure.
3. We cannot drop a table that is referenced by a foreign key constraint.
4. The transaction cannot be rolled back in drop.

Answer 6

1.drop is used to drop the whole table from the database...when use drop the table definitions will go off
2.delete is used to delete one or more tuples from relation (table)...if you use DELETE FROM TABLENAME (relation name) it deletes all the tuples from that table...but table definition will remain the same...