Use of truncate table in SQL
Let us see what exactly is that the truncate table in SQL and what’s the utilization of it.
At the end of this article, I also captured the video explanation for your better understanding, so confirm to observe that video also.
So what’s 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 because the truncate table in SQL server.
Let us consider the below employee table for this instance .
|Emp ID||Emp Name||Address|
Truncate table Employee;
Truncate SQL Practical Example
Once I execute this command in SQL editor where I even have an employee database i will be able to get the message just like the table is truncated.
Now If I write a get query to see this table within the same database.
Select * from Employee;
I could see that no rows selected.
Now If I attempt to rollback. Rollback are going to be completed successfully but again if you write an equivalent query it’ll not show you the table within the database.
Select * From Employee;
You will see no rows selected for this question . So from this, we will understand that if we are using truncate it’s one for all. this suggests if you employ it once, it’s done it cannot rollback.
It is sort of a commit itself.
Forever the rows are getting to be truncated there’s no way that you simply can get the rows back.
Truncate is quicker than delete. Whenever you say delete it goes each and each condition to see 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’s 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’s the delete command? Delete command what exactly goes to do is it deletes the table alongside the where clause.
It deletes only those certain rows supported the condition that we are getting to specify along side the where condition.
If you’re not getting to use where condition if you merely use delete what’s getting to happen is that the entire rows are getting to be deleted.
Let us write a delete statement to delete one record where the department number is 10.
I can write a question something as below?
Delete from Department Where DeptID = 10;
You can see one row deleted as soon as you execute this question .
Now you’ll write a get statement to ascertain that a specific record in department table gets deleted.
Which will not appears within the table.
Here regardless of the things that you simply deleted you’ll rollback.
If you give rollback and again write a get query to ascertain the department number 10. which can be there.
Whenever you employ delete command you’ll use rollback.
Make sure you recognize all the rows that you simply have deleted.
Also, whenever you’re using delete command the triggers which are related to delete query are going to be fired. in order that is another thing with the delete command.
If I simply give delete command with none 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
The drop may be a command that removes the table from the database completely. All the associate columns, indexes, and privileges get deleted.
How you’ll make use of drop by 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 rock bottom of the query.
Now you are trying 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 are going to be there.
When you use the drop it removes the whole 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’re using truncate no triggers are going to be fired and every one .
Even for drop also there’s no triggers are going to be fired. that’s what the main difference.
I hope this information is beneficial to you. Please leave your comment within the comment section below.