SQL TRUNCATE Table

Overview

The TRUNCATE TABLE command in SQL is used to delete the records/rows from a table. It is a Data Definition Language (DDL) command and is the same as the DELETE command without a where clause.

What is TRUNCATE in SQL?

The TRUNCATE statement 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 that data 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, we can say that the TRUNCATE statement is similar to the DELETE statement in SQL without a WHERE clause.

Syntax

The syntax of the truncate statement is:

Arguments

database_name

Name of the database that we are using to truncate the records.

schema_name

The schema name is the name of the schema to which the particular table belongs.

table_name

We need to mention the name of the table to truncate or from which all records are removed.

TRUNCATE TABLE Examples

1. Truncate a Table

Let us consider we have a database(ABC) in which two tables are present, i.e., Customers and Sales. The Customers table has the following five attributes:

Customer ID
Customer Name
Phone Number
Email Address
Item Id

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

The Sales table has the following four attributes:

Item ID
Item Name
Item Price
Items Left

PROPERTY	ATTRIBUTE NAME	DATA TYPE
PRIMARY	ITEM_ID	INT
	ITEM_NAME	VARCHAR
	ITEM_PRICE	INT
	ITEM_LEFT	INT

Given the above two tables, we have to truncate the Customers table.

Explanation:

In Query 1, we visualize the table from the customer's table
In Query 2, we are deleting the customer's table records using the truncate statement.
In Query 3, we will again visualize the customer's table, but now the table will be empty. This is because the data has been deleted by the truncate statement.

TRUNCATE Table vs DELETE Table

DELETE	TRUNCATE
Delete statement is a DML command.	Truncate statement is a DDL command.
Delete statement is used to delete 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.	It removes all rows in a table by deallocating the pages used to store the table data.

TRUNCATE Table vs DROP Table

DROP	TRUNCATE
The DROP statement is used to remove the table contents and its structure from the database.	The Truncate 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.

Conclusion

In this article, we explored the usage of the TRUNCATE statement. The truncate command is used to remove the records of the table.
The Truncate statement cannot be used to remove the records when a Foreign key constraint references a table.
If we are dealing with large tables, we can use Microsoft SQL Server, which can easily manage the large tables.
The advantage of using the Truncate statement over the Drop statement is that when using the drop statement, it removes the table structure as well. Still, a truncate statement only removes the table's records, not the table structure.

See More

Trunc Function in SQL

Learn via video course