SQL Server Truncate Table

When working in a database, you may come across instances where you need to wipe and remove all the data from a given table. Each database system provides different mechanisms to achieve this.

In SQL Server, we have access to the truncate table statement which allows us to do just that.

In this tutorial, we are going to go over the truncate table statement in SQL Server. We will explore the syntax, how it works, compare it similar statements and more.

What is Truncate Table in SQL Server

As mentioned, the truncate table in SQL Server allows us to quickly and efficiently remove all the data from the table while retaining the table schema.

By using the truncate table statement, we can reset the table to a newly created instances with no data which can be useful when you just need to remove the data without re-creating it.

Unlike the DELETE statement, truncate table is significantly faster as it minimizes the logging and does not generate individual row-level delete operations.

This also ensures that it uses minimal system resources compared to the delete statement. Hence, if you need to remove a large dataset without increasing system usage, consider using the truncate table statement.

Lastly, unlike DELETE, TRUNCATE TABLE cannot be rolled back. Once executed, the operation is irreversible, which can be advantageous for certain scenarios.

DELETE vs TRUNCATE TABLE

One of the confusion when working with the truncate table clause is the difference between it and the delete clause.

The following are some key differences between the two

• DELETE is a Data Manipulation Language (DML) operation. Hence, it removes specific rows based on a condition, allowing us to specify which rows to delete.
• TRUNCATE TABLE is a Data Definition Language (DDL) operation. Hence it removes all rows from a table, leaving the table structure intact.
• DELETE is slower and generates more transaction log entries than TRUNCATE TABLE.

How TRUNCATE TABLE Works?

When you execute a TRUNCATE TABLE statement, SQL Server performs the following steps:

• Deallocates all data pages associated with the table, effectively removing the data. This is done by deallocating extents, which are groups of eight data pages.
• Resets identity columns (if any) to their initial seed values.
• Releases any locks acquired on the table.

It is good to note that the truncate table statement uses schema modificiation lock (Sch-M) on the table to prevent concurrent data modification. This includes INSERT, UPDATE, and DELETE statements. Luckily, the lock is compatible with schema locks which allows for concurrent queries and DDL operations.

Truncate Table Statement

The following shows the example usage of the truncate table statement:

TRUNCATE TABLE <table_name>

The command above will truncate the tbale with the specified name in the current schema.

To truncate a table located in a different schema, you can specify the schema name as shown in the syntax below:

TRUNCATE TABLE <schema_name.table_name>

This should remove the data of the specified table in the defined schema.

The truncate table statement does not work on a table with foreign key constraints. You will need to remove them before truncating the table.

Using it in a Transaction

As you can possibly guess, the truncate table does not apply in a native transaction. Hence, you may need to wrap it in another transaction as shown:

BEGIN TRANSACTION;
TRUNCATE TABLE <table_name>;
COMMIT TRANSACTION;

This will execute the specified transaction and truncate the data of the specified table.

Conclusion

In this guide, we explored the truncate table statement when it comes to SQL Server. We learned things like the underlying functionality, syntax, points to note, and more.