Difference between Delete, Drop & Truncate in SQL | DBMS
For database related videos, please subscribe channel
/ @datagurunaval
TRUNCATE
TRUNCATE Command is a Data Definition Language operation. It is used to remove all the records from a table. It deletes all the records from an existing table but not the table itself. The structure or schema of the table is preserved.
TRUNCATE TABLE statement is a DDL command so it can not be rolled back.
The Truncate command resets the AUTO_INCREMENT counters on the table.
DELETE
The DELETE statement in SQL is a Data Manipulation Language(DML) Command. It is used to delete existing records from an existing table. We can delete a single record or multiple records depending on the condition specified in the query.
The DELETE statement scans every row before deleting it. Thus it is slower as compared to TRUNCATE command. If we want to delete all the records of a table, it is preferable to use TRUNCATE in place of DELETE as the former is faster than the latter.
DELETE is a DML Command so it can be rolled back.
The DELETE command returns the number of records that were deleted by its execution.
DROP
DROP statement is a Data Definition Language(DDL) Command which is used to delete existing database objects. It can be used to delete databases, tables, views, triggers, etc.
DROP is a DDL Command. Objects deleted using DROP are permanently lost and it cannot be rolled back.
Unlike TRUNCATE which only deletes the data of the tables, the DROP command deletes the data of the table as well as removes the entire schema/structure of the table from the database.
