NEW T-SQL FEATURES IN SQL SERVER 2016- VIII [ TRUNCATE TABLE WITH ( PARTITIONS ( … ) ) ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – VIII | [ TRUNCATE TABLE WITH ( PARTITIONS ( … ) ) ]

With this command enhancement we can removes all rows from a Table or Partitions(Single/Multiple) of a Table, without logging the individual row deletions. In version before SQL SERVER 2016 we could only truncate a table not the partitions inside it.

Notes:-

1. To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).
2. Partition_number_expression can be given in one of the following ways:

Example of truncate individual partition

--

WITH (PARTITIONS (1))

--

Example of truncate multiple non sequential partitions | Separated by commas

--

WITH (PARTITIONS (3, 5))

--

Example of truncate multiple sequential and non sequential partitions

--

WITH (PARTITIONS (7 , 9 , 11 TO 15 ))

--

Example of truncate multiple sequential partitions | Range

--

WITH (PARTITIONS (5 TO 8))

--

Refer Microsoft LINK below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com