Truncate removes all the rows from a table. Delete with a where clause can delete rows (as specified using where clause) from a table.
Drop will delete a table from the database. Drop and Truncate are DDL commands, whereas DELETE is a DML command.
Please find the below Sample table created to understand how these syntaxes work with example:
Sample Table:
Delete:
The DELETE statement is used to delete existing records in a table.
Syntax:
DELETE FROM table_name WHERE condition;
E.g.: Select * from sample;
Removing the row of empno with 1001 with the help of Delete syntax in SQL:
DELETE FROM sample WHERE Empno=1001;
Explanation:
In the above table we have deleted(removed) the row with empno=1001.
Few points to be noted regarding Delete Syntax:
-
- 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 maintains the log, so it slower than TRUNCATE.
- Identity of column keep DELETE retains 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.
Truncate:
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself i.e. Structure of the table remains same.
Syntax:
TRUNCATE TABLE Table_name;
E.g.: TRUNCATE TABLE sample;
Few points to be noted regarding Truncate Syntax:
-
- 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.
- Truncate cannot be used with indexed views.
- TRUNCATE is faster than DELETE
Drop:
The DROP TABLE statement is used to drop an existing table in a database.
Syntax:
DROP TABLE table_name;
-
- Before dropping the table:
E.g.: DROP TABLE sample;
Explanation:
After using the drop syntax, the table data and structure will be removed, if we select the dropped table we will get as table or view does not exist.
Few points to be noted for Drop syntax
-
- 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.
- The operation cannot be rolled back.
- DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
- DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back
