The TRUNCATE TABLE statement is the most efficient way to remove all rows from a table. Logically, this statement is identical to the DELETE statement without the WHERE clause, but unlike the DELETE statement, when TRUNCATE TABLE has been issued, SQL Server does not log individual row deletion in the transaction log. Therefore, the TRUNCATE TABLE statement is significantly faster. This performance difference can be very important with large tables. However, if you want to enjoy its efficiency, you have to remove all rows from a table. Even if the table is partitioned, you still need to remove all rows from all the partitions. Well, unless you have SQL Server 2016.

In SQL Server 2016, the TRUNCATE TABLE statement has been extended so that you can specify the partitions from which rows have to be removed. You can specify the comma-separated list or the range of partition numbers. Here is a code example showing how to remove all rows from the partitions 1, 2, and 4:

TRUNCATE TABLE dbo.T1 WITH (PARTITIONS (1, 2, 4));

In the next example, you will see how to specify the range of partition numbers. From a table with the maximum number of supported partitions, you want to remove a lot of data, but not all of it—you want to leave data in partitions 1 and 2. Here is the code that implements this request:

TRUNCATE TABLE dbo.T2 WITH (PARTITIONS (3 TO 15000)); 

You can also combine two input formats in one expression. The following code removes all rows from a table with eight partitions, except from the partitions 1 and 3:

TRUNCATE TABLE dbo.T1 WITH (PARTITIONS (2, 4 TO 8)); 

Specifying partitions in the TRUNCATE TABLE statement is possible even if the database is not in compatibility level 130.

To simulate a TRUNCATE TABLE for a specific partition in previous SQL Server versions, you need to perform the following steps:

1. Create a staging table with the same indexes as in a partitioned table.
2. Use the SWITCH PARTITION statement to move data from the partitioned to the staging table.
3. Remove the staging table from the system.

Now you need a single and very efficient statement—a nice and handy feature.