Ok, sql db partitioning and switching doesn't seem to be a highly discussed topic, but it is something I'm implementing. I've had success in all areas of partition functions, partition schema, file group assignments, etc. Up to this point I haven't had any issues with partition switching until a pesky xml field was found to be killing performance. So I attempted to add a simple primary xml partition, but now all the partition switching is failing. Below is a mock of my table structure in play.
CREATE TABLE [MySchema].[DataTable](
[PartitionKey] [varchar](12) NOT NULL,
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DataTabe_Id] DEFAULT NewId(),
[ClientId] [smallint] NOT NULL,
[ProjectId] [int] NOT NULL,
[ResponseValue] [nvarchar](255) NULL,
[ResponseValueXml] xml NULL,
CONSTRAINT [PK_DataTable] PRIMARY KEY CLUSTERED
(
[PartitionKey] ASC
, [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS = ON)
ON PS_MySchema(PartitionKey)
) ON PS_MySchema(PartitionKey)
Some notes on this is that the partition key is a complex / computed key based on clientId, year, and another value. The partition schema is built on a partition function that groups clients + dates, which are then put into seperate file groups (by year) - makes sliding window method easy to manage.
The general flow of things is to create a work table in the same file group, use alter table to switch the desired partition out of DataTable to the work table. Do some stuff against the work table, and switch it back when done. This works great... Until I add a primary xml index on ResponseValueXml. The alter table / switch requires that only the source of the switch be allowed to have an xml index. So, it appears I'm allowed to switch it to the work table (so long as the work table doesn't have an xml index). But I can't seem to switch it back. If I used the DataTable as the source, it errors because the target cannot be empty. If I use DataTable as the target, it errors about the xml index only allowed on a source table.
What am I missing here? How am I supposed to manage this index when I'm not allowed to switch them? Any guidance is appreciated.