Advertisements

Archive

Archive for June 4, 2015

Now truncate “Table Partitions” with TRUNCATE TABLE statement – SQL Server 2016

June 4, 2015 6 comments

TRUNCATE TABLE has got a new definition now!!!
 

Now with SQL Server 2016 as per MSDN BoL: “TRUNCATE TABLE removes all rows from a Table or Specified Partitions of a Table, without logging the individual row deletions”.
 

This is kind of a filtration to TRUNCATE TABLE statement at a Partition level, just like a WHERE clause option with DELETE TABLE statement at a Row level.

With previous versions ( SQL Server 2014 and back) you could only Truncate a whole Table, but not its Partition.
 

–> You can now use a WITH PARTITIONS() option with TRUNCATE TABLE statement to truncate Table Partition(s) like:

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

 

MSDN reference for [TRUNCATE TABLE]
 

Check my [previous post] on differences between TRUNCATE & DELETE statements.


Advertisements