SQL Server: TRUNCATE TABLE Statement

In this post explains how to use the TRUNCATE TABLE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The TRUNCATE TABLE statement is used to remove all records from a table in SQL Server. It performs the same function as a DELETE statement without a WHERE clause.

Syntax

The syntax for the TRUNCATE TABLE statement in SQL Server (Transact-SQL) is:

TRUNCATE TABLE [database_name.] [schema_name.] table_name
[ WITH ( PARTITIONS ( partition_number
                    | partition_number TO partition_number ) ] ;

Parameters or Arguments

database_name

Optional. If specified, it is the name of the database.

schema_name

Optional. If specified, it is the name of the schema that the table belongs to.

table_name

The table that you wish to truncate.

WITH ( PARTITIONS ( partition_number | partition_number TO partition_number )

Optional and can only be used with partitioned tables. If specified, partition_number is the number of the partition that you wish to truncate in the partitioned table. To list multiple partitions, comma separate the partition number values or ranges. If you try to use this clause with a table that is not partitioned, SQL Server will return an error. This feature is not available in all versions of SQL Server.

Note

• If you truncate a table, the counters on any identity columns will be reset.
• You can not truncate a table that is referenced by a Foreign Key.
• Before you can truncate a table, you must have the necessary privileges such as ALTER TABLE.

Example

In SQL Server, truncating a table is a fast way to clear out records from a table if you don't need to worry about rolling back. When a table is truncated, the row deletions are not logged which is why rolling back is not possible. Truncating a table is also a lot easier than dropping the table and recreating it.

Let's look at an example of how to use the TRUNCATE TABLE statement in SQL Server.

For example:

TRUNCATE TABLE employees;

This example would truncate the table called employees and remove all records from that table.

It would be equivalent to the following DELETE statement in SQL Server:

DELETE FROM employees;

Both of these statements would result in all data from the employees table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you can't roll back the TRUNCATE TABLE statement.

Let's look at one more example where we prefix the table name with the database name.

For example:

TRUNCATE TABLE dbd.contacts;

This example would truncate the table called contacts in the database called dbd.

With Partitions

If you want to truncate a specific partition or range of partitions, you can use the WITH PARTITIONS clause.

For example:

TRUNCATE TABLE employees
WITH (PARTITIONS (1 TO 5, 7));

In this example, the employees table is a partitioned table and the TRUNCATE TABLE statement would truncate partitions 1 through 5 as well as partition 7 in this partitioned table.

Frequently Asked Questions

Question: Can you rollback a TRUNCATE TABLE statement in SQL Server?

Answer: A TRUNCATE TABLE statement can be rolled back in SQL Server by using a transaction.

For example:

CREATE TABLE test_table (column1 int);

INSERT INTO test_table VALUES (1);
INSERT INTO test_table VALUES (2);
INSERT INTO test_table VALUES (3);

-- Create a transaction

BEGIN TRAN;

-- Truncate table

TRUNCATE TABLE dbo.test_table;

-- Rollback truncate table

ROLLBACK;

SELECT * FROM test_table;

The SELECT statement above should return the following records:

column1
----------

1
2
3     

As you can see, the TRUNCATE TABLE statement was successfully rolled back and the 3 records are still within test_table.