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.
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.
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.
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
The following are some key differences between the two
DELETEis a Data Manipulation Language (DML) operation. Hence, it removes specific rows based on a condition, allowing us to specify which rows to delete.
TRUNCATE TABLEis a Data Definition Language (DDL) operation. Hence it removes all rows from a table, leaving the table structure intact.
DELETEis slower and generates more transaction log entries than
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.
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.
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.