Friday, October 29, 2004

SQLServer: Partitions

Initial testing with SQLServer 2000 partitions appears to work simillarly with Oracle from a select perspective.

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.