Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

Question

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.

Answer 1

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

• brian: +1 Good find. I'd say disable the index, switch partitions, then rebuild the index. Or drop index, switch partitions, recreate index. Either should work.
• Danny: I was certainly hoping I wouldn't have to do the drop and recreate xml index. That's a really expensive process to perform. Particularly on the switch back to the primary table which will contain about 15 million records. That's why we elected to use partitioning in the first place.
• Danny: @Matt. What I don't seem to grasp is the target table in the switch can't have an xml index. Am I understanding correctly that this means that it can't exist in either target or source, because ultimately it's going to be switched back to the original source, so that what was the source will now be the target. This seems like a crazy limitation to an otherwise great concept.
• Matt M: @DannyGrogan Yes, if you are going to switch back to the original source, there can be no XML index on either the original source or the original target. Could you move the XML column to another table, constrained with a foreign key? Both source and destination tables would need to have this foreign key that references the table containing the XML. This solution could get you around this limitation, if possible in your situation.
• Danny: @Matt. Unfortunately the foreign key would immediately trip when the partition switches. The 'original' concept actually had 3 tables involved, but ran into issues maintaining relationships during partition switches, so we came up with the xml field which allowed us to eliminate the other 2 tables completely. There's got to be some way around this.