SQL Server 2016 - Truncate at Partition level

Enhancements to TRUNCATE in SQL Server 2016:

SQL Server 2016 introduced truncation at partition level without logging at the individual rows.
This is kind of having WHERE clause to DELETE statement.

The new TRUNCATE statement in SQL Server 2016 is:

TRUNCATE TABLE dbo.PartitionedTable
WITH (PARTITIONS (2, 4, 6 TO 8))

Above statement will truncate all the partitions 2, 4, 6, 7 and 8.







Delete and Truncate in SQL

In SQL we have to use DELETE command in order to delete the data.

To completely remove the data from a table we can use TRUNCATE command.

Find below the differences between TRUNCATE and DELETE command

DELETE	TRUNCATE
It's DML command	It's DDL command
It's a row lock. Whenever you fire DELETE command it will lock the rows which satisfies deletion criteria	It will lock table and page but not the row
It removes only the data which satisfies deletion criteria	It removes all the data
WHERE clause is allowed to specify filters	WHERE clause is not allowed
It activates a trigger because the operation are logged individually	It cannot activate a trigger because the operation does not log individual row deletions
Compared to TRUNCATE it is slower because it logs everything	Faster than DELTE as it don't log anything
Rollback is possible	Rollback is not possible. But inside the transaction we can rollback both DELETE and TRUNCATE
It will not reset IDENTITY property	It will reset IDENTITY property

Syntax:

DELETE:

DELETE

FROM

Employee

WHERE FirstName = 'Anil'

It will remove all the rows where FirstName is Anil

TRUNCATE:

TRUNCATE TABLE Employee

It will remove all the data present in Employee table