- 4 years ago
- Zaid Bin Khalid
- 2365 Views
-
3
In this tutorial, you will learn how to quickly remove all rows from a database table by using the SQL statements.
Removing Table Data
The TRUNCATE TABLE statement removes all the rows more quickly than a delete statement from a table, but the table structure and its columns. Constraints, indexes ETC. remain the same but if you want to remove the table you have to add drop TABLE statement.
Syntax
The basic syntax of TRUNCATE TABLE can be given with.
TRUNCATE TABLE table_name;
Now we are performing the truncate Statement on a database
table.
Suppose we have a table called employees table in our database as shown below.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 1 | Zaid Khalid | 2002-05-01 | 5600 | 4 |
| 2 | Tony Montana | 2003-03-15 | 6500 | Null |
| 3 | Jhon Connor | 2005-10-01 | 8060 | 5 |
| 4 | Rick Deckard | 2006-01-03 | 7200 | 3 |
| 5 | Black Blank | 2007-06-01 | 5670 | NULL |
+--------+--------------+------------+--------+---------+
As shown in the below example the command will remove all the rows from the employee’s table.
TRUNCATE TABLE employees;
After executing the above SQL statement, if you use to select the records from the employee’s table the result will show an empty set.
TRUNCATE TABLE vs. DELETE STATEMENT
Both Statements seem to have the same effect but actually they work differently from others. To understand both statement details are mention below.
- However, delete value lets the filter down the deletion of the row by where clause options, whereas the TRUNCATE TABLE doesn’t follow the where clause and it only removes all the rows.
- TRUNCATE TABLE statement typically works as a drop and re-create the table by resetting the auto-increment value to start value by 1.
- TRUNCATE TABLE is Faster and used few System resources than DELETE. Delete Scans the Table to generate a count of rows that were affected then delete the rows one by one and records an entry in the database log for each deleted row. Meanwhile, the TRUNCATE TABLE just deletes all the rows without additional information.
- 4 years ago
- Zaid Bin Khalid
- 2365 Views
-
3