Relational Table Purge issues with Sliding Window Partition

Question

Dear Experts,

I have recently implemented the Sliding Date Range partition for one of the Primary / Foreigin Relational Tables. Partitioning was working fine but I am having a challenge while the Purge partition.

Here is the Table Structure I have

Primary Table: (Table Escalation = AUTO)

Create Table PTable
(PTableID uniqueidentifier NOT NULL ,
Name nvarchar(100),
CreatedDate datetime NOT NULL ,

(constraint PK_PTableID Primary Key Clustered
)
PTableID asc
)

Indexes:
IDX_CreatedDate(NonClustered) with (createddate) aligned to scheme
PK_PTableID (clustered)

Foreign Table: (Table Escalation = AUTO)

Create Table FKTable
(FKTableID uniqueidentifier NOT NULL ,
PTableID uniqueidentifier (Foreign Key reference to PTable (PtableID))
Name nvarchar(100),
Metadataa varchar(200)
CreatedDate datetime NOT NULL ,

(constraint PK_PTableID Primary Key Non-Clustered
)
FKTableID asc,
CreatedDate

)

Indexes:
IDX_CreatedDate (Clustered) (createddate / FKTableID) aligned to scheme
PK_FKTableID (unique/NonClustered)

Here is the Problems now

1. I need to Either Truncate or Siwtch out the Partition as part of the Purge.
2. Doing the same, I am dropping the FK Relation and running a Truncate Partition in the Transaction and adding the FK back in same transaction
3. I am able to do it successfully on the Child table but not on the Primary Table
4. I tried aligning the PK with Partition Key on the Primary table and did the same but not able to create back the FK as it is clubbed with the Primary Key now and not allowing the Foreign Key relationship.
5. These tables are around 30 Million in size for each partition and Delete was never a suggested option. Someone suggested to keep the lock escalation to AUTO and try the Delete in batches it will not lock the table, may be it is a misperception it is still locking the table in this approach and it is a very busy table when per seconds there are 900 inserts happening.

I am puzzled to resolve this above, any help or suggestion on the above would be appreciated.

Thanks
Momen

Answer 1

Partitioning is one of these features that looks cool, but falls apart when you actually try to use it, and you may find yourself in a situation where there are only poor solutions to choose from.

Had PtableID been an integer fed by IDENTITY or a sequence, you could have partitioned on the ID values, but now you have a GUID.

To partition the table on CreatedDate, you need to include that column in all unique indexes. Now you did not partition the clustered index, and thus the table is not partitioned. It would not be any better if you made the PK non-clustered and stored it outside the partition scheme. That wold be a non-aligned index, and it would block TRUNCATE TABLE on partition level.

The reason the partition key must be part of the unique index is quite obvious when you think of it: else SQL Server cannot enforce the uniqueness of the index. That is, you don't want SQL Server to check 15000 partitions on a single INSERT if the key is a duplicate.

And if you promote CreateDate to the PK in PTable, you also need to modify the FK in the other table accordingly.

Obviously adding CreateDate to the PK in Ptable stinks. Now you can get the same GUID many times.

With all my years in SQL Server I have never implemented a partitioned table for an application I have been working with.