SQL Truncate Table Example | Truncate Table In SQL
SQL TRUNCATE TABLE command deletes the data inside a table, but not the table itself. The Truncate Table command deletes the data inside a table, but not the table itself. The DROP TABLE the command deletes a table in the database. Be careful before removing a table and deleting the table results in loss of all information stored in the table.
SQL Truncate Table Statement
TRUNCATE statement is the Data Definition Language (DDL) operation that is used to mark the extents of the table for deallocation (empty for reuse).
The result of this operation quickly deletes all the data from the table, typically bypassing the number of integrity enforcing mechanisms. The truncate was officially introduced in the SQL:2008 standard.
Okay, now let’s see the syntax of the SQL Truncate Table.
TRUNCATE TABLE table_name
Okay, now let’s deep dive into an example.
See the following table.
Now, we will truncate the Apps table using the following query.
TRUNCATE TABLE Apps;
So, it has removed all the data
Now, again type the following query and try to fetch all the records.
Select * from Apps
You will see that there are data left inside the table.
#DROP TABLE and TRUNCATE TABLE
The significant difference between the Drop Table and Truncate Table is that DROP table deletes the table itself, whereas the Truncate Table does not eliminate the Table itself, it removes the data from the table.
You might choose to truncate the table instead of dropping the table and recreating it.
Truncating the table can be faster and does not affect any of the table’s indexes, triggers, and dependencies. It is also a quick way to clear out the records from the table if you don’t need to worry about the rolling back.
So, the DROP TABLE command to delete the complete table, but it would remove the entire table structure form the database, and you will need to re-create that table once again if you wish you store some data still or in the future.
Table or Database deletion using a DROP statement cannot be rolled back, so it must be used wisely.
#DELETE TABLE Vs. TRUNCATE TABLE
Truncate table is much faster and it uses lesser resources than the DELETE TABLE statement.
If the table contains a primary key column, the counter for that column will be reset to a first value. For example, we have ID INT IDENTITY(1, 1) which contains the 100 rows/records, and we performed the TRUNCATE Statement table on ID.
That truncate statement will delete all the rows from ID, and reset the IDENTITY to 1.
If you are using the DELETE TABLE query and remove one id, then the next id will be removed id + 1. This means it does not restructure the whole data.
If the particular id row is gone, then it is gone. That id will not assign to another row in case of DELETE TABLE.
The SQL Truncate Table query deletes all the rows from a specified table, but the table structure, constraints, columns, indexes will remain the same.
Finally, SQL Truncate Table Example is over.