SQL Server 2016: TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement
Related Topic: TSQL Enhancements in SQL Server 2016>
Next Topic: DROP IF EXISTS
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....
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.
- How to Move TempDB Database Files to a New Drive in SQL Server
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Repair Suspect Database in SQL Server
- SQL Server Disaster Recovery Tips for DBAs and Developers
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: May 09, 2016
Please leave your Valuable Comment or Let us know how this article helped you: