SQL TRUNCATE TABLE
SQL TRUNCATE TABLE command deletes all the records in a table while preserving the table structure.
TRUNCATE In SQL
TRUNCATE in SQL means deleting records of a table but preserving its structure like rows, columns, keys, etc. It is considered to be a DDL command instead of a DML command.
SQL TRUNCATE TABLE is used for emptying a table while preserving table structure so that it can be re-populated.
Note: The TRUNCATE command can not be used to Truncate a Column or Database in SQL.
Syntax
TRUNCATE TABLE Syntax is:
TRUNCATE TABLE table_name;
SQL TRUNCATE TABLE Example
Let’s understand TRUNCATE in SQL with examples. Here we will look at different examples of the SQL TRUNCATE TABLE command.
First, we will create a demo SQL database and table, on which we will use the TRUNCATE TABLE command.
CREATE DATABASE w3wiki;
USE w3wiki;
CREATE TABLE EMPLOYEE(
EMP_ID INT(4),
NAME VARCHAR(20),
AGE INT(3),
DOB DATE,
SALARY DECIMAL(7,2));
INSERT INTO EMPLOYEE VALUES(121,'AJAY KUMAR',23,'1987-09-12',23456.32);
INSERT INTO EMPLOYEE VALUES(132,'BOBBY DEOL',34,'1989-02-19',84164.56);
INSERT INTO EMPLOYEE VALUES(246,'ELVISH SMITH',27,'1996-01-29',51876.97);
INSERT INTO EMPLOYEE VALUES(955,'GEORGE CLARKE',31,'1992-09-21',91451.64);
INSERT INTO EMPLOYEE VALUES(729,'MONICA GELLER',28,'1985-11-18',98329.43);
The following table will be created.
Records in the table
TRUNCATE TABLE in SQL Example
In this example, we will Truncate the created table.
Query:
TRUNCATE TABLE EMPLOYEE;
Truncating data
After truncating data of our table, the data of our table has been erased but the structure is preserved so now if we perform SELECT * FROM EMPLOYEE command on our table we will see everything is erased and an empty set is being returned.
No data is returned
But let’s now check whether the structure of the table is deleted or it has been preserved so we again use the DESC command to see the structure of the table and we will see that the structure remains as it is.
Structure is preserved
SQL TRUNCATE vs DELETE
Using the TRUNCATE TABLE command is faster and consumes less memory than using the DELETE TABLE command.
TRUNCATE TABLE is not used with WHERE Clause but DELETE TABLE command can be used with WHERE clause.
SQL TRUNCATE vs DROP
DROP TABLE command removes all records from the table, but it also deletes the structure of the table. The TRUNCATE TABLE command does not remove the table structure.
TRUNCATE TABLE is a DDL (Data Definition Language) command, while DELETE is a DML (Data Manipulation Language) command.
Important Points About SQL TRUNCATE TABLE
- TRUNCATE TABLE is used to empty a table by removing all rows, but it retains the table structure.
- TRUNCATE TABLE is ideal for quickly removing all data from a table without deleting the table structure, making it efficient for data cleanup operations
- TRUNCATE TABLE is faster and uses fewer system and transaction log.
- TRUNCATE TABLE cannot be rolled back within a transaction.
FAQs on SQL Truncate
What is the use of the TRUNCATE command?
TRUNCATE is a DDL command which deletes the whole data of our table without deleting the structure of the table.
What is the difference between TRUNCATE AND DROP commands?
The Truncate command is not same as DROP table command because DROP table command deletes both the data in the table and also the table’s structure whereas TRUNCATE only deletes records.
What is the difference between TRUNCATE AND DELETE command?
TRUNCATE command does not use the WHERE clause while DELETE command does and it is faster than the DELETE command.