SQL Server Truncate Table

The SQL Server truncate statement removes all the records from a table while retaining the table structure. Think of the truncate statement as a way of purging data from a table without dropping the actual table.

In this guide, you will understand how to work with the truncate command in SQL Server, allowing you to reset the tables without recreating them.

SQL Server Truncate Table

When working with the database, you will encounter instances where you need to delete all the data stored in a table without dropping the table.

Although you can use the delete statement without a conditional clause, the truncate command is much faster and efficient.

The syntax of the truncate command is as shown:

TRUNCATE TABLE [database_name.][schema_name.]TABLE_NAME;

Let us take the example queries below that create a table and insert sample data.

CREATE DATABASE product_inventory;
USE product_inventory;
CREATE TABLE inventory (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
product_name VARCHAR(255),
price INT,
quantity INT
);
INSERT INTO inventory(product_name, price, quantity) VALUES
('Smart Watch', 110.99, 5),
('MacBook Pro', 2500.00, 10),
('Winter Coats', 657.95, 2 ),
('Office Desk', 800.20, 7),
('Soldering Iron', 56.10, 3),
('Phone Tripod', 8.95, 8 );
SELECT * FROM inventory;

Run the truncate query to remove all the data from the table:

TRUNCATE TABLE inventory;
SELECT * FROM inventory;

The resulting table is as shown:

Notice that the table structure is still kept while all the data stored is removed.

SQL Server Truncate vs. Delete

You may wonder, if the truncate command is like the delete statement, why do I need to use the truncate command?

The following are some advantages of the truncate command over delete.

It is much faster and more efficient compared to the delete statement.
The truncate command consumes fewer resources than the delete command.
If the truncated table contains an identity column, the truncate command resets the column to the default seed value. This is not the case for the delete command.
The truncate command locks the table rather than individual rows.
The truncate command provides a minimal transaction log compared to delete.
Truncate does not trigger the delete trigger when performing the specified operations.

Conclusion

In this article, you discovered how to work with the truncate table command in SQL Server to purge data from a table without destroying the table structure.

SQL Server Truncate Table