How to Use TRUNCATE Command in SQL?
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.
Table of Content
- What is SQL Truncate Table Command?
- Examples
- Removing all the records using the DELETE command in SQL
- Limitations of Truncate Table command
- TRUNCATE TABLE vs. DELETE TABLE
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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.
Checkout the Top Online SQL Courses and Certifications
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;
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;
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;
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 StudentWITH PARTITIONS (1, 3 TO 5);
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;
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!!
Please Checkout More SQL Blogs
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.
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
Top Picks & New Arrivals
