Some years ago a wrote an article about automatic data purging with partition switching. Having moved to SQL Server 2016 recently I have encountered the new ability to truncate individual partitions, rather than switching data out.
Partitioning
In a previous article I showed a basic example of Partition Switching, using animal names (http://wp.me/p3Vxvi-2u). While not a realistic example for everyday use it did get the principles across. However, certainly in my experience, partitioning is normally dependent upon a number – usually a date. In this article I’ll create a partition process that …
Want to delete a couple of records quickly from a large table; well DELETE is more than capable (indexing issues aside). Want to delete a couple of million from a large table quickly? Now it can get a little more interesting. Partitioning is a very useful tool for moving large amounts of data very quickly. …
Notes on SQL 