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

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


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
  1. clement huge
    November 15, 2015 at 8:08 pm

    Love this new feature! Thanks for pointing it out!

  2. June 4, 2015 at 11:25 pm

    MSDN link would be very helpful, you have done it before.

    • June 4, 2015 at 11:30 pm

      oh, I didn’t?

      here you go… thanks for your comments.

    • Mesay
      June 5, 2015 at 6:44 am

      great work

  1. May 31, 2017 at 10:52 pm
  2. June 4, 2015 at 11:09 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: