Delete, Truncate and Drop Statement In SQL with Example

When we need to manipulate data in a table stored in a database, we can use select, update, delete, truncate, and drop statements.

So in today’s post, we’ll go over the Delete, Truncate, and Drop statements in SQL and try to understand the differences between them.

DELETE

Delete statement is used to remove data on the basis of condition applied in where clause. So lets see the syntax of Delete Statement

DROP FROM table name WHERE clause;

For instance, we want to remove all data from workers whose age is less than 30, In that case we would write the sql query like this.

DELETE FROM workers WHERE age < 30;

So if we see above SQL query, then we can clearly see that delete statement will remove one or more rows which are imbibing the condition provided in where clause and remaining data remain intact which are not fulfilling the condition.

Now we need to remember that delete statement only deletes the data, it is not going make any changes in table structure and indexes created for indexing. Since it is DML statement and it generates change logs as well so if necessary at any point of time, changes can be reverted using rollback statement. But always remember that commit statement has not be executed and running session has not been broken.

TRUNCATE

The TRUNCATE statement is used to remove all the data from a table. Unlike the DELETE statement, TRUNCATE does not have a WHERE clause, and it removes all the data from the table in a single operation.

TRUNCATE TABLE table_name;

For instance, we want to delete all rows from table workers, then here is the sql query

TRUNCATE TABLE workers;

Do you know that truncate statement is much more faster than Delete Statement, So lets list some of the points.

• It does not create logs like Delete statement for all the changes made in tables.
• it does not check rows because truncate query doesn’t have where clause. So no tension of rows count or its stats.
• Truncate statement deallocate all the data pages associated with the tables, it means it clear all the data occupied on the disk and reset the structure of table back to its original state. It never traverse in rows of the tables it directly delete the data on disk and so it is much more faster than Delete statement.

It’s vital to remember that, unlike the DELETE statement, the TRUNCATE statement uses the Data Definition Language (DDL), not the Data Manipulation Language. This indicates that TRUNCATE is irreversible and cannot be undone. TRUNCATE also returns the table’s auto-increment value to 0 (zero).

DROP

Drop statement in SQL is used to delete the entire table from databases and it can’t be recovered because structure and indexing of entire table is removed from the disk. The DROP statement has the following syntax:

DROP TABLE table_name;

For example, we want to delete entire table staff from database we will write following query

Drop table staff;

So let’s list some of the important point regarding Drop statement.

• Drop statement is DDL (Data Definition Language) So it is irreversible, it this statement by mistake you have executes on database sql client means table is gone forever.
• Drop statement always remove data, indexes and constrains associated with tables.

In conclusion, We can say that Delete, Truncate and Drop Statement has its own use case and we should always be careful while using these statement and we should always be clear with the task to achieve.

Hope you have like this post.. Will meet again with next post.