T-SQL - TRUNCATE TABLE
The T-SQL (Transact-SQL) TRUNCATE TABLE statement 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.
Syntax
The syntax of using TRUNCATE TABLE statement 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:
| 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.
