How To Truncate Table In SQL Server

Truncating a table in SQL Server is a rapid and efficient way to remove all the data from a table without deleting the table structure. So, first of all, you should be very careful before trying to execute the TRUNCATE command. Here’s a real-time approach to achieve this.

To truncate a table in SQL Server Management Studio, we just need to use the TRUNCATE TABLE statement.

Check out: How to copy one table to another in SQL Server

Note: Before trying to perform the Truncate operation, ensure that the table is not referenced by any foreign key constraints.

Approach 1: Using the basic TRUNCATE TABLE query

Syntax

Below is the syntax of the truncate table command.

TRUNCATE TABLE Table name;

Example

I have a table named ProductNew containing a record, as shown in the screenshot below.

Let me execute the TRUNCATE TABLE query below and see what happens. The screenshot below shows that the query was executed successfully.

Check out: Create table if not exists SQL Server

TRUNCATE TABLE ProductNew;

Check out: How To Add A Column To A Table In SQL Server

Now, Let us rerun the Select query to check if the data in the table exists. You can see that the data from the table was deleted successfully, but the table still exists. Check out the screenshot below for your reference.

Approach 2: Using the TRUNCATE TABLE query with Partitions

You can use the TRUNCATE TABLE query with Partitions to truncate a particular or even a range of partitions. The syntax is as follows.

Syntax

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

Example

TRUNCATE TABLE Orders
WITH (PARTITIONS (1 TO 3, 5));

Note that the Truncate operations cannot be performed on tables that are referenced by foreign key constraints.

Check out How To Delete All Records From A Table In SQL Server

Conclusion

The TRUNCATE TABLE statement is a simple and efficient tool for quickly deleting all the data from your table while keeping the structure as mentioned in this article!

You may also like following the articles below.

• How to drop temp table if exists in SQL Server
• How to drop temp table if exists in SQL Server
• How To Check If Column Exists In SQL Server Database