How to implement table purge with partition on SQL Server 2012?

I've read this question:
How to remove a table partition

But it's not what I want.

I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, configure a job that executes montlhy and drops the partition(s) that have records over 9 months old.

Can I set this up using SQL Server 2012 Enterprise?

The goal is to reduce the amount of transaction log by only updating metadata.

When data is move or remove from a partition, it Insert and Delete rows which will result in (tons of) LOB_INSERT_ROWS and LOB_DELETE_ROW in the transaction logs.

The only option would be to truncate the partition but this option does not exist.
We can avoid it by only using Merge and Split on empty Partitions.

In the sample below, I will keep things shorter and only create data for the past 3 months (ie. August, September and October) but you can easily expand it to 9 months. August will be removed once data starts being added to November and so on with September and December...

Create Files and Filegroups:

I first create 6 files and file groups [Part_0] to [Part_5]:

Alter Database [Test] Add Filegroup [Part_0];
...
Alter Database [Test] Add Filegroup [Part_5];
Alter Database [Test] Add File( NAME = N'Part_0', FILENAME = N'...\Part_0.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_0];
...
Alter Database [Test] Add File( NAME = N'Part_5', FILENAME = N'...\Part_5.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_5];

Create Function and Scheme:

Create Partition Function [DateKeyPartFunction] (datetime2)
as Range Right For Values ('20150801', '20150901', '20151001', '20151101', '20151201');
Create Partition Scheme [DateKeyPartScheme] as Partition [DateKeyPartFunction]
To ([Part_0], [Part_1], [Part_2], [Part_3], [Part_4], [Part_5]);

Again there are 6 partitions. This will be explained later but this is mostly due to the need to have empty partitions.

Create Table and Clustered Index:

Because I don't know the exact design of your table, I will be working with this table:

Create Table dbo.DataPart(id int identity(0, 1), name char(1000), name_date datetime2);

and this Clustered Index:

Create Clustered Index IDX_Part On dbo.DataPart(name_date) On DateKeyPartScheme(name_date);

Dummy data:

This code creates over a million record in a range of dummy dates every 6 seconds from October to August(now):

With inc(n) as(
    Select ROW_NUMBER() over(order by (select 1))-1 From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x4(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x5(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x6(n)
    ) as x(n)
)
Insert into dbo.DataPart(name, name_date) 
Select TOP(1000000) '', DATEADD(second, -n*6, getdate()) From inc;

Partitioned Data:

This will partionned as follow:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 = '20150801'   = '20150901'   = '20151001'   = '20151101'   = '20151201'                   0

• [Part_1], [Part_2], [Part_3] contain data for August, September and October.

• [Part_0] (ie.

• It make things easier to already have an empty partition for next month although it will remain empty until it reach November. This is the purpose of [Part_4] for November.

• Partition Scheme needs an extra partition for everything outside of the right boundary. This is [Part_5] for December and beyond and it must remain empty as well.

Add November:

Once in November, new rows will go to [Part_4] and August data can be removed from [Part_1].
The only way to remove it without having to delete hundred of thousands of rows is to move [Part_1] away from the table:

Create Table dbo.DataPart_Temp(id int identity(0, 1), name char(1000), name_date datetime2);
Create Clustered Index IDX_Part_temp On dbo.DataPart_temp(name_date) On [Part_1];
Alter Table DataPart Switch Partition 2 to DataPart_temp Partition 1;

• DataPart_Temp must be identical to DataPart (columns, indexes)

• Because [Part_1] is move from DataPart to DataPart_temp, the clustered index on DataPart_temp must be created on the same filegroup: [Part_1]

• [Part_1] is the second partition of DataPart and is switch to the first and only Partition of DataPart_temp. All August rows are now in DataPart_temp.

the table is now partitioned as follow:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 = '20150801'   = '20150901'   = '20151001'   = '20151101'   = '20151201'                   0

Merge Partition:

[Part_0] and [Part_1] are now empty and can be merge:

Alter Partition Function [DateKeyPartFunction]() Merge Range ('20150801');

[Part_1] has been removed:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 = '20150901'   = '20151001'   = '20151101'   = '20151201'                   0

Add Next month:

Now that [Part_1] is not used anymore, i

StackExchange Database Administrators Q#119179, answer score: 5