SQL - TRUNCATE TABLE

The SQL TRUNCATE TABLE statement is used to delete complete data from an existing table. The SQL DROP TABLE statement can also be used to delete complete data of a table but it will delete whole table structure from the database. Hence, SQL TRUNCATE TABLE statement is useful when a table need to be emptied but the table structure is retained.

Note: Be careful before truncating a table. Once deleted, all data stored in that table will be lost forever!.

Syntax

The syntax of using TRUNCATE TABLE statement is given below:

TRUNCATE TABLE table_name;

Example:

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

In databases like MySQL and MariaDB, the description of the table can be checked using DESC command as shown below:

DESC Employee;

This result of the above code will be:

Field	Type	Null	Key	Default
EmpID	int(11)	No	PRI
Name	varchar(255)	No
City	varchar(100)	Yes		NULL
Age	int(11)	Yes		NULL
Salary	decimal(18,2)	Yes		NULL

Similarly in SQL Server, the description of the table can be checked using EXEC sp_columns command as shown below:

EXEC sp_columns Employee;

This result of the above code will be similar to:

TABLE_OWNER	TABLE_NAME	COLUMN_NAME	DATA_TYPE	TYPE_NAME	PRECISION	LENGTH
dbo	Employee	EmpID	4	int identity	10	4
dbo	Employee	Name	12	varchar	255	255
dbo	Employee	City	12	varchar	100	100
dbo	Employee	Age	4	int	10	4
dbo	Employee	Salary	3	decimal	18	20

To truncate this table, the SQL code is shown below:

TRUNCATE TABLE Employee;

After truncating the table, the DESC command will still show the same structure as shown above but the table will contain no records.