Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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.

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.
–
DannyDec 20 '11 at 16:42

@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.
–
DannyDec 20 '11 at 16:52

@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.
–
Matt MDec 20 '11 at 19:26

@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.
–
DannyDec 20 '11 at 22:05