SQL Truncate

SQL Truncate is one of the Data Definition Language (DDL) commands. It deletes all the records in a database table. However, it does not delete the table’s structure; instead, it simply reconstructs the structure. The table’s original structure is retained while all the records are removed. Because it reconstructs the structure of a database, it is considered to be a DDL command instead of a DML command.

The DROP table command removes both the structure and the contents of the table. This is where the DROP command differs from the truncate command. Truncate is similar to DELETE in that it deletes the table’s structure and contents. The only difference is that it uses a WHERE clause instead of DELETE and executes faster.

Why do we need to use the truncate command?

We may have saved the wrong data in the database and now we need to change it while preserving the structure of the table so that we don’t have to rebuild the table.

TRUNCATE Command

Below mentioned is the syntax of Truncate Command.

Syntax

TRUNCATE TABLE table_name;

How to Perform TRUNCATE Operation?

Step 1: Creating a database

To start with first we need to create our database so that we can store tables in it. To create a database we use the CREATE DATABASE command. Let’s create a database named GeeksForGeeks.

Query:

 CREATE DATABASE GEEKSFORGEEKS;

Creating a database

Step 2: Selecting the database

Now after creating the database, to start working with the database we need to select it first. To select or use a database we use the USE command Let’s now select our database GeeksForGeeks.

Query:

USE GEEKSFORGEEKS;

Using database

Step 3: Creating a table

Now that we have selected our database we can proceed with creating tables in our database. To create tables in a database we use the CREATE TABLE command. Let’s now create a table named Employee in our database.

Query:

 CREATE TABLE EMPLOYEE(
    EMP_ID INT(4),
    NAME VARCHAR(20),
    AGE INT(3),
    DOB DATE,
    SALARY DECIMAL(7,2));

Creating a table

Step 4: Inserting Data

As we have created a table in our database we can now proceed with inserting data in the table. To insert data in a table we use the INSERT INTO command. Let’s now insert some data in our table Employee .

Query:

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);

Inserting data in the table

Now to look at the records of data in the table we use the SELECT * FROM EMPLOYEE command.

Records in the table

Step 5: Truncating data

we have inserted data in the table now we can use the TRUNCATE command to erase all the data of the table by preserving the structure of the table. To truncate data in a table we use the TRUNCATE TABLE command. Let’s now truncate data in our table Employee.

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

TRUNCATE TABLE Vs DELETE TABLE

Using the truncate table command is faster and consumes less memory than using the DELETE table command.

TRUNCATE TABLE Vs DROP TABLE

You can also use the drop table command to remove the entire table, but it also removes the table structure. The TRUNCATE table command does not remove the table structure.

Conclusion 

In this article, we have learned what is TRUNCATE command in SQL is and why it is used. Also, we have learned how to Truncate data from our table in a step-by-step manner. As summary, TRUNCATE is DDL command thats deletes the whole data of our table without deleting the structure of the table, it is faster than both the DROP and DELETE command.

FAQs on SQL Truncate

Q.1: What is the use of the TRUNCATE command?

Answer:

TRUNCATE is a DDL command which deletes the whole data of our table without deleting the structure of the table.

Q.2: What is the difference between TRUNCATE AND DROP commands?

Answer:

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.

Q.3: What is the difference between TRUNCATE AND DELETE command?

Answer:

TRUNCATE command does not use the WHERE clause while DELETE command does and it is faster than the DELETE command.