Sql-server – Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

sql-server-2008

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.

Best Answer

This is straight from BOL: Transferring Data Efficiently by Using Partition Switching

XML columns must have the same schema. Any xml columns must by typed to the same XML schema collection.

No XML indexes allowed on the target table. There can be no XML indexes on the target table.

It appears that you need to ensure both XML columns are typed against the same schema and drop the XML index from the target partition, if applicable.

I have never personally performed a partition switch when an XML index is involved, so there could be more to it than this.

Hope this helps,

Matt

Related Question