How to Use TRUNCATE Command in SQL?

How to Use TRUNCATE Command in SQL?

5 mins read5.1K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 12, 2024 12:04 IST

The SQL TRUNCATE TABLE command is designed to remove all records from a table. While it may seem similar to the SQL DELETE statement, which also removes records from a table, there are some differences between the two commands. This article will briefly explore the SQL TRUNCATE TABLE command and how it differs from the SQL DELETE statement.

2022_08_MicrosoftTeams-image-235.jpg

Table of Content

What is SQL Truncate Table Command?

TRUNCATE TABLE command in SQL is used to remove all the records from the table or specified partitions of a table. Once the TRUNCATE command is used, you can’t recover the data even using ROLLBACK.

It is similar to the DELETE command in SQL without a WHERE clause.

  • TRUNCATE TABLE command is a Data Definition Language (DDL) command.
  • Do not fire the triggers.
  • It only removes the table data, while the table definition remains the same.
  • It is faster than the DELETE command as it removes the record without logging the individual row deletions.

Must Read: Introduction to SQL

Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views

Syntax for TRUNCATE command in SQL

The syntax for SQL TRUNCATE TABLE is just a one-line command TRUNCATE TABLE followed by the table_name you want
i.e.


 
TRUNCATE TABLE table_name;
Copy code

Now let’s have some examples to understand better how to use the SQL TRUNCATE TABLE command.

Examples

So, firstly we will create a student table of a class that contains the record of a student who scored maximum marks in a particular subject using the CREATE TABLE  and INSERT command in SQL.


 
CREATE TABLE Student(
RollNo int PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Gender TEXT NOT NULL;
Subject VARCHAR(30),
MARKS INT (3)
);
INSERT INTO Student VALUES (1, Vaibhav, M, Mathematic, 100);
INSERT INTO Student VALUES (2, Vishal, M, Physics, 79);
INSERT INTO Student VALUES (3, Saumya, F, Chemistry, 95);
INSERT INTO Student VALUES (4, Arun, M, English, 78);
INSERT INTO Student VALUES (5, Anjum, F, Hindi, 83);
INSERT INTO Student VALUES (6, Radhika, F, Biology, 57);
INSERT INTO Student VALUES (7, Harpreet, F, Physical Education, 68);
SELECT * FROM Student;
Copy code

Must Read: SQL SELECT

Output:

RollNo Name Gender Subject Marks
1 Vaibhav M Mathematics 100
2 Vishal M Physics 79
3 Saumya F Chemistry 95
4 Arun M English 78
5 Anjum F Hindi 83
6 Radhika F Biology 57
7 Harpreet F Physical Education 68

Must Read: SQL Logical Operator

Example 1: Use the TRUNCATE TABLE command in the above student table to remove all the records.

Query


 
TRUNCATE TABLE Student;
SELECT * FROM Student;
Copy code

Output

No Data Found

Must Read: Introduction to Normalization

Example 2: TRUNCATE TABLE command over a partition

Remove the record of columns 1 and 3 TO 5.

Query


 
TRUNCATE TABLE Student
WITH PARTITIONS (1, 3 TO 5);
Copy code

Output

RollNo Name Gender Subject Marks
NULL Vaibhav NULL NULL NULL
NULL Vishal NULL NULL NULL
NULL Saumya NULL NULL NULL
NULL Arun NULL NULL NULL
NULL Anjum NULL NULL NULL
NULL Radhika NULL NULL NULL
NULL Harpreet NULL NULL NULL

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

How to remove all the records using the DELETE command in SQL?

Using the DELETE command, we can remove all the records from the table without affecting the table structure, attributes, and indexes.

Query


 
DELETE FROM Student;
Copy code

Limitations of TRUNCATE TABLE

TRUNCATE TABLE command in SQL is not used when:

  • EDGE constraint is used to refer to the table
  • The FOREIGN KEY constraint is used to refer to the table
  • CREATE EXTERNAL TABLE statement is used to define the table
  • A command is not allowed to be used inside a transaction

Must Read: SQL LIMITS

Must Check: Database and SQL Online Course and Certifications

TRUNCATE TABLE vs. DELETE TABLE

TRUNCATE TABLE DELETE TABLE
It is a DDL command. It is a DML command.
Remove all rows from the table without using the WHERE clause Remove all or specific rows from the table using the WHERE clause
Remove all the records at a time by delocating the pages that are used to store the data. It removes one row (record) at a time.
It can’t be used with the indexed views. It can be used with the indexed views.
Reset the identity to its seed value. It retains the identity and doesn’t reset it to the seed value.

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties

Conclusion

This article briefly discussed the TRUNCATE TABLE command in SQL and how to use it. We also cover how it is different from the SQL DELETE statement. In the following article, we will discuss the DROP TABLE command in SQL, its importance, and how it differs from the DELETE and TRUNCATE TABLE commands.
I hope this article will help you in your Data Science/Data Analysis Career.
Keep Learning!!
Keep Sharing!!

Must Read: Joins in SQL

Must Read: SQL INNER JOIN

FAQs on How to Use Truncate Command in SQL

What is the basic syntax for the TRUNCATE TABLE statement?

The basic syntax varies slightly across different databases. For example:

  • SQL Server: TRUNCATE TABLE [schema_name.]table_name [WITH (PARTITIONS...)].
  • Oracle: TRUNCATE TABLE [schema_name.]table_name [PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG] [DROP STORAGE | REUSE STORAGE].
  • PostgreSQL: TRUNCATE [TABLE] [ONLY] name [*] [, ...] [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]

What's the difference between TRUNCATE and DELETE?

TRUNCATE removes all records without a WHERE clause, can't be rolled back, doesn't generate an undo log, and doesn't cause delete triggers to fire. It is a DDL command. On the other hand, DELETE can target specific records, be rolled back, generate an undo log, lock rows during deletion, and trigger delete triggers. It is a DML command.

Can TRUNCATE TABLE be used in a stored procedure?

Yes, you can use TRUNCATE TABLE inside a stored procedure. However, you might need to grant the necessary permissions to the procedure to execute the TRUNCATE TABLE command.

How does TRUNCATE TABLE affect system resources compared to DELETE?

TRUNCATE TABLE is more resource-efficient than DELETE. It uses fewer transaction logs and locks, as it deallocates data pages rather than removing each row individually​

Are there any limitations to using TRUNCATE TABLE?

Yes. You can't use TRUNCATE TABLE on tables with foreign key constraints or that are involved in replication or indexed views. It's also not possible to apply conditions to TRUNCATE operations, and the action is irreversible without a backup.

What is the impact of the TRUNCATE TABLE on identity columns?

In some database systems, TRUNCATE TABLE resets the value of an auto-increment column (or identity, sequence, etc.) to its starting value. This is not the case with the DELETE statement.

How can data be recovered after using TRUNCATE TABLE?

Data recovery after a TRUNCATE operation usually involves restoring from database backups. Advanced database systems might offer log-based recovery if transaction logs are maintained and haven't been overwritten​.

Can TRUNCATE TABLE be rolled back?

TRUNCATE TABLE operations can't be rolled back unless used within a transaction that has not been committed.

What are the speed and efficiency comparisons between TRUNCATE and DELETE?

TRUNCATE is inherently faster than DELETE due to its bulk operation nature, involving deallocating data pages instead of removing rows one by one, which also reduces logging.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments