Overview: In this tutorial you will understand TRUNCATE SQL command, which is use to delete all records from the table without deleting the structure of Table.
What is SQL TRUNCATE command ?
It is the statement which is mostly used to delete all records from the table, Truncate command statement delete all rows from a table without deleting the structure of Table. So that you can import another data into the same table if structure remain the same.
Note:
You can also use DROP TABLE command to delete complete table. However it would remove complete table structure from the database and you will not able to see the Table again in database.
Syntax for SQL Truncate
TRUNCATE TABLE [database_name] table_name;Example: SQL Truncate command for table
Lets take example to delete all records from Customer_Table table which is in the databse name CustomerDatabase.
TRUNCATE TABLE CustomerDatabase.Customer_Test_Table;Once you will delete all records from table you can see the table again using SELECT command in SQL and It will show you only the structure of table without any data. meaning only show the header of table but row items will be zero.
You can use below code also to TRUNCATE table in SQL by using Database and then execute TRUNCATE TABLE command
USE CustomerDatabase
TRUNCATE TABLE CustomerDatabase.Customer_Test_Table;TRUNCATE vs DELETE
If you want to delete particular rows or few rows from SQL table then you need to use DELETE command in SQL instead of SQL TRUNCATE command.
| Truncate | Delete |
|---|---|
| It locks the entire table | It locks the table row. |
| Its DDL (Data Definition Language) Command | Its DML (Data Manipulation Language) command |
| Can’t use WHERE clause with it. | Can use WHERE to filter data to delete |
| Trigger is not fired while truncate | Trigger is fired |
| Truncate reset identity of table | Delete does not reset identity of table |
When Not to Use Truncate in SQL
The SQL Truncate command is used to remove the records from the SQL tables. In some situations, the TRUNCATE command cannot change data records.
Below are the restriction:
- Participate in an indexed view.
- You can truncate a table that has a foreign key that references itself.
- An EDGE restriction is used to refer to them.
- Transactional replication or merge replication are used to publish the data.
- Temporally system-versioned.
- TRUNCATE TABLE isn’t allowed to be used inside a transaction.