Difference between delete truncate and drop in sql :
In previous articles i have explained about difference between views and complex-views,difference between set operators.In This article i will give you the difference between the three important commands used in SQL-Truncate,Delete and drop with real life examples.In 90% of interviews the question is been asked- What is Difference between Truncate and Delete,Drop in SQL?Interviewer always expects the answer in brief.In this article you will get the brief idea about difference between truncate and delete,drop in SQL.This article is useful for the users who needs to find out Difference between delete truncate and drop in sql. The Difference between delete truncate and drop in sql is explained with real industry scenarios so that user will understand it quickly and will use it in real programming.
Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.
SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL. The difference between TRUNCATE, DELETE, and DROP is one of the most common interview questions. TRUNCATE, DELETE, and DROP SQL queries are often used in SQL Server to delete data from a database. The difference between DROP and DELETE table is that, after executing DELETE statement the contents of the table are removed but the structure remains the same, but in the case of the DROP statement both the contents and structure are removed.
I am giving the Difference between delete truncate and drop in sql in quite different form.I will try to explain different statements with example and you will get the Difference between delete truncate and drop in sql in bullet points.
Truncate:
1.Truncate is Data Definition Language command which is used to remove the all Rows from the table.
2.You can not Filter rows while truncate data from the database because it does not allows where clause.
3.Truncate does not return number of rows truncated from the table.
4.Truncate deallocates the memory for that object and other object will use that deallocated space.
5.Truncate operation can not roll backed because it does not operates on individual row.It directly processes all rows from the table.
6.Truncate is faster than delete.
7.You can not use conditions in case of truncate.
8.truncateit will do 2 actions
1.drop the table from db
2.after that it will recreate the object with metadata
it releases space occupied by the rows, we can use that space for another purpose by using reuse command. (Suggested By Mohan from Readers )
Syntax:
Truncate table <Tablename>;
Real Life Example:
Suppose you want to delete or remove all records from table named department which has following table structure:
Name of Table: Department
| Department ID | Employee Name |
| 100 | Amit |
| 100 | Rohan |
| 101 | Rohit |
| 102 | Null |
Truncate table Department;
So the all values are truncated.
If we want to check the count of that table:
Select count(*) from Department;
0 rows displayed
Use Roll back and check the Count:
Rollback;
Select count(*) from Department;
0 rows displayed
After Rollback statement also the count is Zero.So truncate table is used to truncate all the rows from the table.This sections gives you information about the Truncate statement in SQL
Delete:
1.Delete is Data Manipulation Language statement which is used to manipulate the data from the table.
2.Delete Statement does not change any property of database.
3.Delete statement is used to remove the rows from the table.you can use filtering criteria or where condition to remove the specific rows from the table.
4.If You can not specify the where condition all rows will be removed from the table.
5.After using delete you need to commit the changes to make it permanent.
6.It deletes the row by row data from the table so Delete is slower than truncate.
7.Every deleted row is locked,thus it requires more number of locks.
8.This operation uses all Delete triggers.
Syntax:
Delete from tablename
Where column name= condition;
Drop And Truncate Difference
Real Life Example:
Delete Drop Truncate Difference In Sql
Suppose you want to delete or remove the records where Employee name is ‘Amit’ and ‘Rohan’
Name of Table: Department
| Department ID | Employee Name |
| 100 | Amit |
| 100 | Rohan |
| 101 | Rohit |
| 102 | Null |
Delete from Department where Employee Name is in(‘Amit’,’Rohan’);
Output:
| Department ID | Employee Name |
| 101 | Rohit |
| 102 | Null |
When you Rollback the transaction:
Rollback;
Then it will rollback all the records from the table:
| Department ID | Employee Name |
| 100 | Amit |
| 100 | Rohan |
| 101 | Rohit |
| 102 | Null |
Drop:
1.Drop is Data Definition Language Statement.
2.The Drop command removes the table from the database.
3.It removes all the indexes,privilleges,rows and frees the memory space for other objects.
4.You can not drop the table referenced by foreign key constraint.
5.The objects dependent on the table which we are dropping like Views,procedures needs to be explicitly dropped.
6.No DML triggers will be fired.
7.You can not roll back the drop table operation.
8.drop it will delete the db object permanently from db like tables, (Suggested by Mohan by Readers)
Syntax:
Drop table tablename;
8.To drop table with all its constraints and references:
Syntax:
Drop table tablename cascade constraint purge;
Real Life Example:
Suppose you want to drop the following table.
Name of Table: Department
| Department ID | Employee Name |
| 100 | Amit |
| 100 | Rohan |
| 101 | Rohit |
| 102 | Null |
Drop table Department;
Then If you check firing select statement:
Select * from Department;
Output:
Error at line 1
Ora-00942- table or view does not exist
Hope you will get exact idea about the three statements Drop Truncate and delete statement.If you like this article dont forget to comment.
Difference between delete truncate and drop in sql in Tabular format :
| Truncate | Delete | Drop |
| Truncate is DDL Command | Delete is DML Command | Drop is also DDL Command |
| Truncate is executed using table lock. Whole table is locked while removing the records. | DELETE is executed using a row lock, each row in the table is locked for deletion. | The DROP command removes a table from the database. |
| We cannot use Where clause with TRUNCATE. | We can use where clause with DELETE to filter & delete specific records. | We cannot use Where clause with Drop. |
| TRUNCATE removes all rows from a table. | The DELETE command is used to remove rows from a table based on WHERE condition. | All the tables’ rows, indexes and privileges will also be removed. |
| Minimal logging in transaction log, so it is performance wise faster. | It maintains the log, so it slower than TRUNCATE. | It maintains the log, so it slower than TRUNCATE. |
| Truncate cannot be rolled back. | User can roll back the deleted data before committing it. | The operation cannot be rolled back. |
Hope Everyone like this article on Difference between delete truncate and drop in sql. I have tried to explain it with different kind of real life scenarios so that user will get the idea of it.Kindly comment in comment section if you have any suggestions regarding the same.
DELETE and DROP are the commands used to remove the elements of the database. DELETE command is a Data Manipulation Language command whereas, DROP is a Data Definition Language Command. The point that distinguishes DELETE and DROP command is that DELETE is used to remove tuples from a table and DROP is used to remove entire schema, table, domain or constraints from the database.
Let us discuss some more differences between DELETE and DROP command in SQL with the help of comparison chart below.
Content: DELETE Vs DROP
Comparison Chart
| Basis for Comparison | DELETE | DROP |
|---|---|---|
| Basic | DELETE remove some or all the tuples from a table. | DROP can remove entire schema, table, domain, or constraints from the database. |
| Language | DELETE is a Data Manipulation Language command. | DROP is a Data Definition Language command. |
| Clause | WHERE clause can be used along with the DELETE command. | No clause is used along with DROP command. |
| Rollback | Actions performed by DELETE can be roll-backed. | Actions performed by DROP can not be rollbacked. |
| Space | Even if you delete all the tuples of the table using DELETE, space occupied by the table in the memory is not freed. | Table deleted using DROP frees the table space from memory. |
Definition of DELETE
DELETE is a Data Manipulation Language (DDL) command. DELETE command is used when you want to remove some or all the tuples from a relation. If WHERE clause is used along with the DELETE command, it removes only those tuples that satisfy the WHERE clause condition.
If WHERE clause is missing from the DELETE statement then by default all the tuples are removed from the relation, though the relation containing those tuples still exist in the schema. You can not delete an entire relation or domains or constraints using DELETE command.
The syntax of DELETE command is as follow:
DELETE FROM relation_name WHERE condition;
If you link two tables using a foreign key and delete a tuple from a referenced table then automatically the tuple from referencing table will also be deleted in order to maintain the referential integrity.
To maintain referential integrity, DELETE has two behavioural options, RESTRICT and CASCADE. RESTRICT reject the deletion of tuple if it referenced by a referencing tuple in another table. CASCADE allows deletion of the referencing tuple that refers the tuple being deleted.
Definition of DROP
DROP is a Data Definition Language (DDL ) command. The DROP command removes the named elements of the schema like relations, domains or constraints, you can even remove an entire schema using DROP command.
The syntax of DROP command is as follow:
DROP SCHEMA schema_name RESTRICT;
DROP Table table_name CASCADE;
The DROP command has two behavioural options named CASCADE and RESTRICT. When CASCADE is used to DROP the schema, it deletes all the related elements like all relations in the schema, domains and constraints.
When you use CASCADE to remove a relation (table) from a schema, then it deletes all the constraints, views and also the elements that reference the relation that is being dropped.
In case you DROP a Schema using RESTRICT then, the DROP command executes only if the no elements in the schema are left. If you DROP a table using RESTRICT, then the DROP command will execute only if no elements in the table are left.
Key Differences Between DELETE and DROP in SQL
- DELETE command is used to remove some or all the tuples from the table. On the other hand, the DROP command is used to remove schema, table, domain or Constraints from the database.
- DELETE is a Data Manipulation Language command whereas, DROP is a Data Definition Language command.
- DELETE can be used along with the WHERE clause but, DROP is not used along with any command.
- Actions performed by the DELETE command can be rollbacked, but not in the case of DROP command.
- As DELETE command do not delete the table hence, no space is freed whereas, DROP deletes the entire table frees the memory space.
Conclusion
DELETE command is used to delete the rows inside a table and the DROP command is used to delete the complete table itself.
Related Differences: