SQL TRUNCATE TABLE Keyword
The SQL TRUNCATE TABLE keyword 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.
Syntax
The syntax of using TRUNCATE TABLE keyword 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 | Extra |
---|---|---|---|---|---|
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.
❮ SQL Keywords