Use of truncate table in SQL
Let us see what exactly is the truncate table in SQL and what is the use of it.
At the end of this article, I also captured the video explanation for your better understanding, so make sure to watch that video as well.
So what is truncate table in SQL?. In one word It simply removes all the rows from a table.
The syntax for truncate as follows.
Truncate Table < Table Name>
Truncate table MySQL is same as the truncate table in SQL server.
Let us consider the below employee table for this example.
|Emp ID||Emp Name||Address|
Truncate table Employee;
Truncate SQL Practical Example
Once I execute this command in SQL editor where I have an employee database I will get the message like the table is truncated.
Now If I write a select query to check this table in the same database.
Select * from Employee;
I could see that no rows selected.
Now If I try to rollback. Rollback will be completed successfully but again if you write the same query it will not show you the table in the database.
Select * From Employee;
You will see no rows selected for this query. So from this, we can understand that if we are using truncate it is one for all. This means if you use it once, it is done it cannot rollback.
It is like a commit itself.
Forever the rows are going to be truncated there is no way that you can get the rows back.
Truncate is faster than delete. Whenever you say delete it goes each and every condition to check whether the condition is satisfied or not.
If the condition is satisfied it removes the row.
If it is not satisfied it retain the row.
Truncate blindly delete all the rows that is why truncate is more faster than the delete.
Remember that rollback doesn’t work in case of truncate.
Apart from this truncate command, there are two other important commands in SQL. Which are delete and drop?
These are most used SQL commands and also these commands are being asked in interviews very frequently.
SQL Truncate vs Delete vs Drop
- Truncate – Which deletes all the rows from a table cannot rollback.
- Delete – Wich delete rows based on certain condition and it can be rollbacked.
- Drop – Deletes table from the database
Use of delete table in SQL
Let us consider the sample department table for this example.
|Dept ID||D Name||Location|
So what is the delete command? Delete command what exactly is going to does is it deletes the table along with the where clause.
It deletes only those certain rows based on the condition that we are going to specify along with the where condition.
If you are not going to use where condition if you simply use delete what’s going to happen is the entire rows are going to be deleted.
Let us write a delete statement to delete one record where the department number is 10.
I can write a query something as below.
Delete from Department Where DeptID = 10;
You can see one row deleted as soon as you execute this query.
Now you can write a select statement to see that particular record in department table gets deleted.
Which will not appears in the table.
Here whatever the things that you deleted you can rollback.
If you give rollback and again write a select query to see the department number 10. Which will be there.
Whenever you use delete command you can use rollback.
Make sure you know all the rows that you have deleted.
Also, whenever you are using delete command the triggers which are associated with delete query will be fired. So that is another thing with the delete command.
If I simply give delete command without any where condition what happens is all the rows get deleted.
Delete from Department;
Again we rollback and try to write a select query on department. The table will appears.
Use of Drop table in SQL
Drop is a command that removes the table from the database completely. All the associate columns, indexes, and privileges get deleted.
How you can make use of drop in SQL is
Drop table table_name;
Let us consider the customer table for this example.
|Cust ID||Cust Name||Pincode|
Syntax for drop command in sql is as follows.
Drop table table_name;
Let us consider customer table for this example.Now to drop this table my query will be
Drop table Customer;
As soon as you execute this command you see the table dropped at the bottom of the query.
Now you try to access the table you see table or view doesn’t exists.
Whereas in truncate it removes all the rows but the structure of the table will be there.
When you use the drop it removes the entire table with the structure of that table.
Make sure that you remember whenever you are using delete the trigger associated with the table will be fired if it is there.
Whenever you are using truncate no triggers will be fired and all.
Even for drop also there is no triggers will be fired. That is what the major difference.
I hope this article is useful to you. Please leave your comment in the comment section below.