T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - TRUNCATE TABLE Keyword



The T-SQL (Transact-SQL) TRUNCATE TABLE keyword is used to delete complete data from an existing table. The T-SQL (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 keyword in T-SQL (Transact-SQL) is given below:

TRUNCATE TABLE table_name;

Example:

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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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_OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPETYPE_NAMEPRECISIONLENGTH
dboEmployeeEmpID4int identity104
dboEmployeeName12varchar255255
dboEmployeeCity12varchar100100
dboEmployeeAge4int104
dboEmployeeSalary3decimal1820

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.


❮ T-SQL Keywords