MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter


















Trending SQL Server Tips








SQL Server 2016: TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement

Read Comments   |   Related Topic: TSQL Enhancements in SQL Server 2016 > Next Topic: DROP IF EXISTS


Click here to read about new T-SQL enhancements in SQL Server 2016 from the beginning...

TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016

It is a well known fact that TRUNCATE TABLE is faster than DELETE statement. TRUNCATE TABLE uses lesser system resources and transactional log file when compared to a DELETE statement.

Starting SQL Server 2016, Microsoft has introduced a new clause TRUNCATE TABLE WITH PARTITIONS which can be used to truncate all rows available within a partition.

Syntax: TRUNCATE TABLE WITH PARTITIONS

TRUNCATE TABLE [SchemaName].[TableName] 
WITH (PARTITIONS [Partition Number Expression] | [Range]);						

"Before you use TRUNCATE TABLE WITH PARTITION clause in SQL Server 2016 make sure all the indexes on the table are aligned with its partitions else the statement will fail."

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

Example Truncate Table with Partitions

Execute the below sample code to remove Partitions 12, 14, 16, 17 and 18 in SQL Server 2016 using TRUNCATE TABLE WITH PARTITIONS clause.

TRUNCATE TABLE Sales.TransactionHistory 
WITH (PARTITIONS (12, 14, 16 TO 18))
GO

Click the Next Page button to continue reading about New T-SQL enhancements in SQL Server 2016 and click on the Previous Page button to revise the previouly read topic.

Previous Page... T-SQL Enhancements in SQL Server 2016 for Developers and DBAs Next Page... DROP IF EXISTS T-SQL Enhancement in SQL Server 2016




Learn More...





Last Updated On: May 09, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: