SQL Server - TRUNCATE TABLE

The SQL Server (Transact-SQL) TRUNCATE TABLE statement is used to delete complete data from an existing table. The SQL Server (Transact-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, 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 in SQL Server (Transact-SQL) 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

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 query is given 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.