SQL TRUNCATE Table - Scaler Topics

The TRUNCATE TABLE command in SQL is a Data Definition Language (DDL) operation used to delete all records from a table swiftly. It functions similarly to the DELETE 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.

TRUNCATE in SQL Syntax

The syntax of the truncate statement is:

Example

Let us consider we have a database(ABC) in which a Customers tables is present. 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

Lets insert some values in our Customer table.

Now the table will look like this -

CUSTOMER_ID	CUSTOMER_NAME	PHONE_NUMBER	ITEM_ID	EMAIL_ADDRESS
1	Ram	98452852822	101	ram12@gmail.com
2	Kumar	9934342422	102	kumar33@gmail.com
3	Gaurav	9434239856	103	gaurav22@gmail.com
4	Shyam	9999786756	104	shyamji@gmail.com

Now, we have to truncate the Customers table.

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.

TRUNCATE vs DELETE in SQL

DELETE	TRUNCATE
Delete statement is a DML command.	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.

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.

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 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.