TRUNCATE TABLE in SQL

Think that you have a large amount of data in SQL and you need to remove all the rows from the table. The catch is that you also need to maintain the structure of the table. The TRUNCATE TABLE command allows you to perform it easily.

In this article, we will see the implementation and working of TRUNCATE TABLE in our database and how it is different from DELETE and DROP commands in SQL.

Table of Content

TRUNCATE TABLE SQL
Example of TRUNCATE TABLE
TRUNCATE vs DELETE
TRUNCATE vs DROP
Key Points About SQL TRUNCATE TABLE
Conclusion

TRUNCATE TABLE SQL

In SQL, the TRUNCATE TABLE command is used to remove all the rows of the table at once without deleting its structure. Removing all the rows from the table quickly makes it much faster, as it directly eliminates the data without logging each row for deletion in the DELETE command.

Make sure that the TRUNCATE command is used to remove rows only, not the columns or database, in SQL.

TRUNCATE TABLE Syntax

We use the TRUNCATE TABLE statement and pass the name of the table in which you want to delete the rows.

TRUNCATE TABLE table_name;

Example of TRUNCATE TABLE

Let’s create a table named INTELLIPAAT, where we are going to have three columns: ID, Name, and Course. Where ID will be a primary key.

CREATE TABLE INTELLIPAAT (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Course VARCHAR(50)
);

Now, we will insert some values in the table.

INSERT INTO INTELLIPAAT (Name, Course) 
VALUES 
('Aryan Raj', 'Data Science'),
('Harry Potter’, 'Cloud Computing'),
('Peter Parker', 'Cyber Security');

Print the table
SELECT * FROM INTELLIPAAT;

Now, we will remove all rows and reset the identity by using the TRUNCATE command.

TRUNCATE TABLE INTELLIPAAT;

Let’s print the updated table.
SELECT * FROM INTELLIPAAT;

TRUNCATE vs DELETE

Below is the difference between TRUNCATE and DELETE statements.

TRUNCATE TABLE	DELETE
It removes all the rows present in the table	It removes the specific rows instead of removing all rows
We cannot perform RollBack in the TRUNCATE TABLE	We can use Rollback in DELETE if we are using it in a Transactions
It resets auto-increment	It does not reset auto-increment
We cannot execute triggers	We can execute triggers
It is faster than DELETE	It is slower as compared to TRUNCATE

You can use the TRUNCATE command when you want to remove all the rows quickly. When you want to remove any specific rows by passing condition, you can use the DELETE command

TRUNCATE vs DROP

TRUNCATE TABLE	DROP TABLE
It removes all rows from the table but the structure remains the same.	It deletes the entire table including its structure.
returns the seed value, if it exists, to the auto-increment counter.	The entire table is eliminated, so there is no effect.
Tables that are referenced by foreign keys cannot be truncated.	A table that is referenced by other tables cannot be dropped unless the foreign key constraint is removed first.
ALTER permission is needed on the table.	DROP permission is needed on the table.

You can use TRUNCATE if you want to remove all rows but keep the table to add new values further. If you want to remove the entire table completely, then use DROP, as it removes the rows as well as the structure of the table.

Key Points About SQL TRUNCATE TABLE

TRUNCATE TABLE is faster than DELETE because it does not log each row for deletion.
You cannot perform Rollback after performing TRUNCATE in most of the databases.
It resets the auto-increment value to start from 1 again.
TRUNCATE might not work because of Foreign key constraints.
It does not trigger ON DELETE triggers.

Conclusion

Mastering the basic SQL commands, including TRUNCATE TABLE, is crucial, as using them without precautions can lead to data loss. Be careful and know what command you need to use in what situation. As we know, if you want to delete the entire rows but not the structure and columns of a table, we can use TRUNCATE TABLE.