Explain plans show accessing only local partition indexes, and not all partitions (or tables) in the union views. Inserts / updates appear to first check each partition, then perform the transaction only on the necessary partition.
Some of the issues we've specifically found with partitions are all the nitpicky rules associated with them.
- Underlying tables must have a primary key and the partition constraint must be part of it.
- Doesn't seem to work with between all the time with the optimizer.
- Must be running Enterprise Edition of SQLServer
Hmm.... Now that's about the same cost as Oracle ;)
And so on, and so on, and so on...
It'll be nice when SQLServer catches up with Oracle from a partitioning perspective, but at least it's a start.
