What is Difference between delete truncate and drop in sql | Truncate Delete Drop

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.User can also have look on advantages and disadvantages of sql.

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.truncate======it 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

CLICK HERE TO GET INFORMATION ABOUT NORMALIZATION

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.

CLICK HERE TO CHECK INFOSYS INTERVIEW QUESTIONS

Syntax:

Delete from tablename

Where column name= condition;

Real Life Example:

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

CLICK HERE TO CHECK ORACLE 18 C ARTICLES

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.

HOME