---
title: SQL TRUNCATE Table - Scaler Topics
description: The TRUNCATE TABLE statement in SQL is a DDL (Data Definition Language) command used to delete the records/rows from the table. Learn more on Scaler Topics.
author: Dev Kumar
category: SQL
---

:::section{.abstract}

The TRUNCATE TABLE command in SQL is a Data Definition Language ([DDL](https://www.scaler.com/topics/ddl-in-dbms/)) operation used to delete all records from a table swiftly. It functions similarly to the [DELETE](https://www.scaler.com/topics/delete-query-in-sql/) command without a WHERE clause. The SQL TRUNCATE TABLE statement efficiently clears all records from a table in a single operation, providing a faster alternative to deleting records individually.
Unlike the DROP TABLE command, the truncate in SQL effectively resets the table without removing its structure, which deletes the entire table structure. Learn more about this statement in our comprehensive guide.

:::

:::section{.main}
## What is a TRUNCATE Command in SQL?

The TRUNCATE in SQL is a DDL (Data Definition Language) command, which is used to delete the records/rows from a table in our database.

When we store some data in our SQL table, it stores it in a SQL server. The SQL server stores the table's data in the form of pages. When the truncate statement is called for the deletion of records, it deallocates the pages from the server, and all the records are deleted.

Moreover, the TRUNCATE statement is similar to the DELETE statement in SQL without a WHERE clause.

:::

:::section{.main}

## TRUNCATE in SQL Syntax

The syntax of the truncate statement is:

TRUNCATE TABLE table_name;

### Example
Let us consider we have a database(ABC) in which a Customers tables is present.
The Customers table has the following five attributes:
1. Customer ID
2. Customer Name
3. Phone Number
4. Email Address
5. Item Id

CREATE TABLE CUSTOMERS (
   CUSTOMER_ID INT NOT NULL,
   CUSTOMER_NAME VARCHAR (20) NOT NULL,
   PHONE_NUMBER  VARCHAR NOT NULL,
   ITEM_ID INT NOT NULL,
   EMAIL_ADDRESS VARCHAR (40),       
   PRIMARY KEY (CUSTOMER_ID)
);

| PROPERTY | ATTRIBUTE NAME | DATA TYPE |
|:-------:|:-------------:|:-------:|
| PRIMARY | CUSTOMER_ID | INT |
| | CUSTOMER_NAME | VARCHAR |
| | PHONE_NUMBER | VARCHAR |
| | ITEM_ID | INT |
| | EMAIL_ADDRESS | VARCHAR |

Lets insert some values in our Customer table.

INSERT INTO CUSTOMERS VALUES 
(1, 'Ram', '98452852822', 101, '[e-mail]' ),
(2, 'Kumar', '9934342422', 102, '[e-mail]' ),
(3, 'Gaurav', '9434239856', 103, '[e-mail]' ),
(4, 'Shyam', '9999786756', 104, '[e-mail]' );

Now the table will look like this -

| CUSTOMER_ID | CUSTOMER_NAME | PHONE_NUMBER | ITEM_ID | EMAIL_ADDRESS|
|:-------:|:-------------:|:-------:|:------:|:-------:|
|1|Ram|98452852822|101|[e-mail]|
|2|Kumar|9934342422|102|[e-mail]|
|3|Gaurav|9434239856|103|[e-mail]|
|4|Shyam|9999786756|104|[e-mail]|

Now, we have to truncate the Customers table.

TRUNCATE TABLE CUSTOMER;

**Output:**

We are deleting the customer's table records using the truncate statement. Now, the table will be empty. This is because the truncate statement has deleted the data.

:::

:::section{.main}
## TRUNCATE vs DELETE in SQL

| DELETE | TRUNCATE |
| :-: | :-: |
| Delete statement is a [DML command](https://www.scaler.com/topics/dml-in-dbms/). | Truncate in sql is a DDL command. |
| Delete statement deletes all the rows specified using the `WHERE` clause. | The Truncate statement removes all the records from the table. |
| It is slower than the TRUNCATE command. | It is faster than the DELETE command. |
| It removes one record at a time. | Truncate in sql removes all rows in a table by deallocating the pages used to store the table data. |
|Manual COMMIT is required after executing DELETE command to commit modifications.|The TRUNCATE command automatically commits modifications to the table.|
|Each transaction is logged individually for record-keeping purposes.|The only activity recorded is deallocating data storage pages.|
|It consumes a greater amount of transaction space than the TRUNCATE command.| The TRUNCATE command is more efficient in transaction space usage than the DELETE command.|

:::

:::section{.main}
## TRUNCATE vs DROP

While both TRUNCATE in sql and DROP commands are categorized under Data Definition Language (DDL) operations and affect the definitions of database objects, they have distinct functionalities. TRUNCATE removes all rows from a table, effectively resetting its data content while keeping the table structure intact. On the other hand, DROP completely removes the table from the database, freeing up the associated memory space. Notably, both commands automatically commit the changes upon execution, making them irreversible without the option to roll back.
| DROP | TRUNCATE |
| :-: | :-: |
| The DROP statement removes the table contents and its structure from the database. | The truncate in sql statement removes all the records from the table. |
| The DROP statement deletes/invalidates the view associated with that table. | No change occurs on the existing views. |
| This command is quicker to perform. | Truncate statement is faster than DROP statement. |
|It is slower than Truncate but faster than the Delete command|It is faster than both Delete and Drop commands.|

:::

:::section{.summary}
## Conclusion
In this article, we explored the usage of the `TRUNCATE` statement. The truncate command removes table records.
- The truncate in sql statement cannot be used to remove the records when [a Foreign key](https://www.scaler.com/topics/sql/foreign-key-in-sql/) constraint references a table.
- If we are dealing with large tables, we can use Microsoft SQL Server, which can easily manage the large tables.
- A truncate statement removes the table's records, not the table structure.

:::