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
