TRUNCATE TABLE in SQL Server 2016

Hello Friends!

SQL Server Database Administration in my core expertise. During my journey from Jr. to Sr. DBA and so on experienced many situations where I preferred using the TRUNCATE TABLE statement against DELETE statement. Reason is simple speed, minimally logged and don’t unnecessarily eats my server resources. However, same statement made me (still does) nervous to get executed in production environment. Simple reason TRUNCATE TABLE statement deletes complete set of rows due to absence of WHERE clause that can be added to the statement to specify the delete criteria. With previous SQL Server versions ( SQL Server 2014 and previous) we can only TRUNCATE a complete Table unlikely from partition(s). SQL Server 2016 is addressing same. 🙂

With the help of WITH PARTITIONS() option with TRUNCATE TABLE statement, we can achieve TRUNCATING table with small chunks based upon partition numbers, which covers my back for sure. 🙂

TRUNCATE TABLE dbo.BankAccountNumbers
WITH (PARTITIONS (10, 12, 14, 16 TO 18))

Quick link to read more about TRUNCATE TABLE options in SQL Server 2016 (and previous versions).

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

Avatar

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published. Required fields are marked *