nisargupadhyay87

Posted on Apr 8 • Updated on Apr 19

How to effectively use TRUNCATE TABLE in SQL Server

Data manipulation in an SQL Server or any other database system includes various operations to manipulate existing data or add new data to a table. These operations contain various DML statements like INSERT, UPDATE, and DELETE. The INSERT statements are used to add new data to a table, the UPDATE statements are used to update the existing data in a table, and the DELETE statements are used to remove data from the table.
Along with the above statements, there are a couple of other statements, like TRUNCATE TABLE, that can be used to manipulate the data.

The TRUNCATE TABLE statement removes all records from any table or specific partition. The purpose of DELETE and TRUNCATE is the same, but both work differently. The DELETE statement is used to remove all records or specific records from the table, but the truncate table is used to remove everything from a table. I have explained the difference between both statements in a separate section of this article.
This article covers the basics of TRUNCATE TABLE with syntax and practical examples. Note that the article is based on the Microsoft SQL Server database. The syntax might be different for other database platforms.

Understanding TRUNCATE TABLE syntax

The syntax to truncate a table is following:

TRUNCATE TABLE [schema_name.table_name] WITH ( PARTITIONS ( partition_number))

In the syntax,

schema_name: Specify the schema name in which the table exists.
table_name: Specify the table name that you want to truncate.
partition_number: Specify the partition number from which you want to truncate the data.

Now, let us understand the syntax with various examples.

Step-by-Step guide to using TRUNCATE TABLE

But before that, we must create a same database that we can use to test our scenarios. I have created a database named BansalGroup_MainDB. The database is used to store the details of the employees and client of a company named Bansal Group. The database has three tables named BansalGroupClients, BansalGroupEmployees and BansalGroupProjects. The BansalGroupClients table holds the data of the clients, BansalGroupEmployees table holds the data of employees and BansalGroupProjects table holds the data of the projects that are being handled by the company.
The BansalGroupProjects table has a foreign key on client_id column which references the BansalGroupClients table.
Here is the T-SQL code to create the tables.

CREATE TABLE BansalGroupProjects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), client_id INT, start_date DATE, completion_date DATE, budget DECIMAL(10, 2), FOREIGN KEY (client_id) REFERENCES Clients(client_id) ); CREATE TABLE [HR].BansalGroupEmployees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), job_title VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) ); CREATE TABLE BansalGroupProjects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), client_id INT, start_date DATE, completion_date DATE, budget DECIMAL(10, 2), FOREIGN KEY (client_id) REFERENCES Clients(client_id) );
Once tables are created, run following queries to insert dummy data in all tables.

INSERT INTO BansalGroupClients (client_id, client_name, contact_person, contact_email, contact_phone) VALUES (1, 'ABC Corporation', 'John Smith', 'john@example.com', '123-456-7890'), (2, 'XYZ Corp', 'Jane Doe', 'jane@example.com', '987-654-3210'), (3, 'LMN Ltd', 'Sam Brown', 'sam@example.com', '555-555-5555'); INSERT INTO [HR].BansalGroupEmployees (employee_id, employee_name, job_title, email, phone) VALUES (1, 'Alice Johnson', 'Architect', 'alice@example.com', '111-222-3333'), (2, 'Bob Williams', 'Project Manager', 'bob@example.com', '444-555-6666'), (3, 'Charlie Brown', 'Draftsman', 'charlie@example.com', '777-888-9999'); INSERT INTO BansalGroupProjects (project_id, project_name, client_id, start_date, completion_date, budget) VALUES (101, 'Office Building', 1, '2023-01-01', '2023-06-30', 500000.00), (102, 'Residential House', 2, '2023-02-15', '2023-09-30', 700000.00), (103, 'Shopping Complex', 3, '2023-03-20', '2024-01-31', 1200000.00);

Now, we are ready to test the various use cases but before that, let us understand the difference between the TRUNCATE Table and other data deletion methods.

TRUNCATE TABLE vs. other SQL Data Deletion methods

The TRUNCATE TABLE and DELETE statements are used to remove data from the table. But both methods work differently and there are several differences between them. Note that, this is one of the popular interview questions. The following are the differences:

Drop table statement:

•DROP TABLE statement deletes a table from database.
•If the table is not locked by any transaction, the drop table statement executes immediately.
•DROP TABLE statement is a DDL statement and they are auto-committed so if you drop any table, it cannot be rolled back.

Truncate Table Statement

•The Truncate table statement deletes all records from a table.
•The TRUNCATE TABLE statement works faster because when we execute TRUNCATE TABLE statement, the operation is not logged in transaction logs which reduced the additional IO which occurs while writing the transaction on log files.
•The TRUNCATE TABLE statement resets the identity.
•The TRUNCATE TABLE cannot be rolled back, hence before executing the statement, make sure you have required backups.
•The TRUNCATE TABLE do not fire a trigger created on a table.

DELETE Statement

•The DELETE statement deletes all records or specific records. You can specify the condition in WHERE clause. So, the records that are matches with the condition will be deleted.
•The DELETE statement is slower, specially when you are removing a lot of records from a table because the DELETE is a logged operations so the changes will be written to transaction log file which adds additional IO.
•The DELETE statement does not reset the identity.
•If DELETE statement is executed in a transaction, and if the changes are not committed, the transaction can be rolled back.
•The DELETE statement executes a trigger created on a table from which you are deleting the data.

Now, let us take a look at some additional recommendation and limitation of using TRUNCATE TABLE.

Special considerations and limitations of TRUNCATE TABLE

The Truncate table statement cannot be executed on:

A table which being referenced by a foreign key. I have explained that in above section.
A table which being used in indexed view.
A table which is being used in merge or transactional replication.
A system-versioned temporal tables.

Before executing a TRUNCATE TABLE statement on any table, you must understand following implications.

As I mentioned in earlier sections of the article, the TRUNCATE TABLE is a minimally logged operation. Hence the changes made in data are not logged in transaction log file. If you truncate a table or partition, the operation could not be rolled back. You must restore entire database.
If the truncate table statement is in a transaction, and you try to rollback the transaction, the DML statements will be rolled back automatically but the truncate table won’t be rolled back.
Always test the procedure or the application code that has TRUNCATE TABLE statement on development environment before executing them on production environment.
Make sure you have well-defined and well-tested backups in your database system. Backups must be validated and tested on regular intervals.

Practical examples in SQL Server Database

Now, let us take a look at some examples. I have created BansalGroup_Maindb on the demo environment. If you are working in a production environment, to keep your data safe, make a backup of the database from which you want to truncate data.

Example 1: Simple Truncate table statement

First, let us truncate BansalGroupEmployees table. To do that, execute following T-SQL query.
use BansalGroup_MainDB go truncate table [BansalGroupEmployees]

Once query executed, run SELECT query to view data.
use BansalGroup_MainDB go select * from [BansalGroupEmployees]

Query output

As you can see, the data has been deleted from the BansalGroupEmployees table. In next example, we will truncate table of specific schema.

Example 2: Truncate table from specific schema

In this example, we will truncate BansalGroupClients table. The table is created in Clients schema, so the command to truncate the BansalGroupClients table would be:

use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]
Once query executed, run SELECT query to view data.
use BansalGroup_MainDB go select * from [clients].[BansalGroupClients]
Query screenshot

As you can see, the data has been deleted from the BansalGroupClients table. In next example, we will truncate table that is referenced by foreign key.

Example 3: Truncate table that is referenced by foreign key

This example shows how to run truncate table statement on a table that is referenced by foreign key. In this example, we will truncate table named BansalGroupClients table. The client_id column of the table is being referenced by client_id column of BansalGroupProjects table. Let us execute truncate table statement.
use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]
Query output

As you can see, the truncate table statement is failed because it is being referenced by another table. To rectify this issue, first we must drop the foreign key on BansalGroupProjects table. Execute following query to drop the foreign key constraint.

alter table [dbo].[BansalGroupProjects] drop constraint [FK__BansalGro__clien__4CA06362]
Once constraint is dropped, re-run the TRUNCATE TABLE statement.
use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]

The query executed successfully. To verify, run SELECT statement on BansalGroupClients table.

use BansalGroup_MainDB go select * from [clients].[BansalGroupClients]

Query output

As you can see, the table has been truncated.

Partitions and permissions

Working with partitions

The TRUNCATE TABLE statement can be used to truncate data from a specific partition. I explained the syntax in an earlier section. For demonstration, I created three partitions on the BansalGroupProjects table. You can read the article to learn more about SQL Server table partitioning.
You can view the details of partitions created on a table by running the following query:

SELECT [tables].name AS [Table Name], [partitions].partition_number AS [Partition Number], [Filegroups].name AS [FileGroup Name], [partitions].rows AS [Records in partition] FROM sys.tables AS [tables] INNER JOIN sys.indexes AS [indexes] ON [tables].object_id = [indexes].object_id INNER JOIN sys.partitions AS [partitions] ON indexes.object_id = [partitions].object_id AND indexes.index_id = [partitions].index_id INNER JOIN sys.allocation_units AS [allocationUnits] ON [partitions].partition_id = [allocationUnits].container_id INNER JOIN sys.filegroups AS [Filegroups] ON [allocationUnits].data_space_id = [Filegroups].data_space_id WHERE [tables].name = 'BansalGroupProjects' ORDER BY [tables].name, [indexes].name, [partitions].partition_number;
Query output

As you can see, there are four partitions in a table. Let us truncate the data from partition number 3. To do that, execute the following SQL query.
use BansalGroup_MainDB go TRUNCATE TABLE BansalGroupProjects WITH (PARTITIONS (3));
Once truncate table completes successfully, execute below query to verify the changes.

The highlighted record in the above image shows that the partition's data has been truncated.

Permissions

You must assign an ALTER permission on the table to the user or sql login. If the user or sql login is already a member of the sysadmin, db_owner or db_ddladmin fixed role, it will automatically get the TRUNCATE TABLE permission on all tables created in a database.

Conclusion

This article taught us about the TRUNCATE TABLE statement in SQL Server and how to use it effectively in SQL Server. The TRUNCATE TABLE can be run using SQL Server Management Studio, SQLCMD utility, or [dbForge Studio for SQL Server] tools.

I have explained the data manipulation operations in any database system. We understand the syntax of the TRUNCATE TABLE statement and various examples. I have covered the difference between a TRUNCATE TABLE query and a DELETE query, which is the most common interview question. Also, we understand when we should use the truncate table and what needs to be taken care of before using it.

DEV Community